How to make a JPA query with LEFT OUTER JOIN

Based on these two tables (and their corresponding entities) :

profiles(id, email, name)
items(id, profile_id, rank, price)

Profile(email, name)
Item(profile, rank, price)

I have to list all the profiles, ordered by the best rank of their items (it's a "top profile" list in fact).

Here is the SQL request like you can execute it in PHPMyAdmin for example:

SELECT AVG(i.rank) AS rank, p.* FROM profiles AS p LEFT OUTER JOIN items AS i ON p.id = i.profile_id GROUP BY p.id ORDER BY rank DESC;

I'm new to JPA and I can't find some examples about doing a LEFT OUTER JOIN with CriteriaBuilder (if it's the right thing to do).

I would really appreciate if someone would lead me to the right way (I'm not asking someone to do me the job, just having good clues).

Thank you very much! :)

5
задан Cyril N. 20 January 2011 в 17:22
поделиться