I have and object c and it has a collection of a. I need a query to returns all instances of c who have the a colletion empty.

In SQL I would write something like this:

select c.* from c , a
where c.id= a.fk_id(+)
and a.id is null

How do I write it in HQL?

I tried using having count(a) =0 but I was returned the error message : path expression ends in a composite value

Can anyone help me?

i'm not sure but i think id is a protected name

c.id

try to replace id with another name (need some rework)

and i repeat i'm not sure, i may be wrong

that is not the name of the field/property it is only to illustrate the case

From the docs:



There might be more efficient ways though.

BTW: What kind of freaky sql syntax is that? That should be an outer join I suppose?

I'm debuging the application and Hibernate's SQL bothers me.

It seems that after it executes the statements that produce the results, it also runs lots of other "selects" in different tables. I don't know why it does that all I know is my DBA wants me dead.

Do you know what is the most efficient to implement this query?


PS: The previous SQL is Oracle's outer join. I wasn't familiar with that sintax until recently :)

Enable lazy fetching or outer join fetching for associations, use join fetch in HQL, read the docs.