CODEWITHSUNDEEP
hibernatecollectionshql
user2133266
user2133266

Reputation: 31

Selecting collections in HQL

HQL noob here, really struggling with this one.

Lets say I have the following three classes:

public class A {
int id;
B b;
}

public class B {
int id;
Set<C> c;
}

public class C {
int id;
String type;
}

Table B has a many to many relationship with C, defined by the table b_c. I need a query that selects from Table A, groups by C.id, filters by C.type = 'desiredType', and returns the C object and the count of A.id. I've been playing around with something like:

SELECT c, COUNT(a.id) as count from A a JOIN a.b.c as c WHERE c.type = 'desiredType' GROUP BY c.id ORDER BY COUNT(a.id) desc

I've played around with different iterations of this query, but I keep getting various exceptions or my query will return nothing. The main problem I seem to be not understanding is how to return the individual objects of a collection that match the correct type.

I hope this question doesn't sound stupid. I've read through the HQL manuals and a hundred other SO questions, but I must be missing something. Thanks in advance for any guidance.

Upvotes: 3

Views: 1711

Answers (1)

Johanna
Johanna

Reputation: 5293

What you want to do does not work in this way. Finally you want to select from A and B but only a subset of B.c which fits your condition. This is not possible. Hibernate only can load the complete set c (not 100% correct, you can limit the select in your mapping, but that is no solution here). In this way there is a possibility, which probably does not fulfil your requirements. You load all A restricted by the exists clause and count manually:

FROM A a where EXISTS (SELECT 1 FROM C, B_C
                          WHERE C.type = 'desiredType'
                             AND B_C.c_id = C.id
                             AND B_C.b_id = a.b.id)

Then you have to go through in a loop over all elements of your lists, which are instances of A, check in an inner loop over all a.b.c if that C is of the desired type (still has to be done; the upper select only restricts the selection to that A which have at least one C of the desired type, but on your list you have all C for these A) and set the count for the right c.id.

The disadvantage of that solution is, all C are loaded even if they have the wrong type, and all counting has to be done in Java code. So you might not like that solution.

The better solution is:

Begin your thinking with table C. In class C you add a Set <B> b; (it is a many to many relation; perhaps it is more handy to define the join table B_C as a Java class). In class B you add the access to table A, either add A a (if B:A is a 1:1 relation) or Set<A> a (if B:A is a 1:n relation). Then you just select the desired C: 

FROM C WHERE C.type = 'desiredType'

The count of A group by C.id you just get in Java for each selected c by countA = c.b.size() (1:1 relation between A and B) or countA = 0; for (B b : c.b) {countA += b.a.size()} (n:1 relation between A and B).

Upvotes: 2

Related Questions