Hi,

We have a model that is basically A which has collection of B, each of which has a collection of C.

We need to query for the objects where the value of B.value is multiple values, and c.value is also multiple values. Thus we need to "flatten" out the hierarchy so we can select on multiple columns. However, this is resulting in humungous queries, and I'm trying to figure out a better way.

As an example, say we have an object A with a collection of the following objects:

- B1.value = m
B1.setC:
C1.value = 1
C2.value = 2

- B2.value = m
B2.setC:
C3.value = 1
C3.value = 2
C4.value = 5

- B3.value = n
B3.setC:
C5.value = 4
C6.value = 6

Now, I need to find all A objects where B.value = m and C.value = 1 and C.value =2.

Not too bad, you say, as we can join against C twice ie A join A.B as b1 join b1.C as c1 join b1.C as c2 where b1.value="m" and c1.value=1 and c2.value=2

The trouble is, we need to be able to search for up to 7 conditions which all have to be true (B.value and C.value can have any number of values). This means that we have to join 8 times, and then have a huge list of where conditions. To get some kind of performance, we have to make sure duplicates are not created by adding WHERE clauses to get rid of them ie WHERE c1.value <> c2.value AND c1.value <> c3.value AND c2.value <> c3.value. You can imagine how many of those there are for 8 joins!

So, I'm thinking there MUST be a better way? Please help!

cheers,

David