2

it's my first post so I hope you help me to find a solution to my problem :)

I have 2 columns, idParent and idChild. If I want to represent that Parent id=1 has 2 children (id=2 & id=3), we find:

+--------------------+
| idParent   idChild |
+--------------------+
|  1           2     |
|  1           3     |
+--------------------+

Well, my goal is starting from a idChild, find all his "top" parents (parents that have no parents) in a single query, if is possible.

Sample:

+--------------------+
| idParent   idChild |
+--------------------+
|    1          3    |
|    2          4    |
|    2          5    |
|    3          5    |
|    3          6    |
|    5          7    |
+--------------------+

Expected result (if we start to querying with idChild=7):

+----------+
| idParent |
+----------+
|        1 |
|        2 |
+----------+

I don't know if I can solve my problem with a single query. My other option would be create a easy query that returns the parents from a child and invoke it many times since my Java code (much more inefficient).

Thanks!

Note: If it helps, I have to use CriteriaQuery API to access data.

Jacob
  • 14,463
  • 65
  • 207
  • 320
david9ppo
  • 75
  • 9
  • Here is a similar question that might help: http://stackoverflow.com/questions/17676944/finding-a-top-level-parent-in-sql – Dennis van Opstal Mar 10 '16 at 13:08
  • That's not possible as far as I know. You can only use the full power of SQL without the obfuscation layer. –  Mar 13 '16 at 12:35

1 Answers1

-2
criteriaBuilder.withRecursive(CteName.class)
.from(EntityName.class, "tableAlias1")
.bind("idParent").select("table_name.idParent")
.bind("idChild").select("table_name.idChild")
.where("table_name.idChild").eq("7")
.union()
.from(EntityName.class, "tableAlias2")
.bind("idParent").select("table_name.idParent")
.bind("idChild").select("table_name.idChild")
.where("tableAlias2.idChild").eq("tableAlias1.idParent");

then u can use the resulting
object.getList();

Big Z
  • 1
  • 1