HQL - selecting parent of a collection with common property

hlfsousa · **Posted:** Mon Sep 18, 2006 3:12 pm

Hi, everyone!
I am trying to create a query that is simpler said than done. There are two entities, A and B, with a bidirectional many-to-one relationship. I want to select all instances of A for which all B have a boolean property equal to true. My best try was this one, but it failed returning as many repeated instances of A as there were B with that property equal to true.

Code:

select a from A join a.b b where b.someProp = true

This query clearly does not contain as much information as I want, but I don't know how to do it. Should I use some cross products?

dtrott · **Joined:** Wed Oct 05, 2005 3:36 am **Posts:** 15

select DISTINCT a from A join a.b b where b.someProp = true

hlfsousa · **Posted:** Tue Sep 19, 2006 7:20 am

Yes, but that gives me all A for which any B.someProp is true. I need all B.someProp to be true. Like this:

Code:

A1
  b[0].someProp = false
  b[1].someProp = true
A2
  b[0].someProp = true
  b[1].someProp = true

I want to select A2 but not A1. After the first post I found a way to do this with five cross-products since A2 has a discriminator attribute with five possible values. But is there a shorter way?

dtrott · **Joined:** Wed Oct 05, 2005 3:36 am **Posts:** 15

FROM a WHERE true = ALL (SELECT b.someProp FROM b WHERE b.a = a)