Hierarchical selects. Parent-child relationship in the same table

Question

Hierarchical selects. Parent-child relationship in the same table

Navigation

#1 by (0 votes)

0

I have a Product Entity with @ManyToOne. And I need to bring the last child record of each parent. I made a select in the table and it is bringing what I need but I am not sure to do the same query with hql.

This is my select in mysql:

SELECT p1.id,p1.description,p1.code,p1.note,p1.owner_id FROM
product AS p1 LEFT JOIN product AS p2
ON p1.id = p2.owner_id
WHERE p2.owner_id IS NULL;

And this query and how I'm trying to do it in hql:

("SELECT E FROM " +  Product.class.getSimpleName()
                + " E LEFT JOIN " +  Product.class.getSimpleName()
                + " P ON E.id = P.owner_id"
                + " P WHERE P.owner_id IS NULL");

java hibernate jpa

asked by anonymous 16.03.2017 / 17:28

1 answer

BarCode 2de5 in GeneXus Compile error in this line of code

score 0 · Answer 1

Try changing your hql to:

("SELECT E FROM " +  Product.class.getSimpleName() + " AS E"
    + " LEFT JOIN " +  Product.class.getSimpleName() + " AS P"
    + " ON E.id = P.owner_id"
    + " WHERE P.owner_id IS NULL");