Hi,
Code:
select cat, count( elements(cat.kittens) ) from eg.Cat cat g
This example from documentation returs only cats which have at least one kitten but I need it to return even cats without kittens among the results (eg. count = 0).
Question1: How to accomplish that (and working independently of used DB)?
I have created such HQL:
Code:
select cat, count(kitten.id) from eg.Cat cat left join cat.kittens kitten group by cat
That would be fine, BUT I have a where condition on the retrival of kittens:
<bag name="kittens" where="some condition">...</bag>
This condition is added into WHERE clause of the generated SQL (I'm using MySQL dialect):
Code:
select cat.*, count(kitten.id) from cat left join kittens on (cat.id=kittens.parent_id)
where some condition;
Instead of placing the where condition inside the ON condition where is the appropriate place:
Code:
select cat.*, count(kitten.id) from cat left join kittens on (cat.id=kittens.parent_id and some condition);
Question2: Is this a bug or a feature?
I have even tried to solve this problem using createSQLQuery(), but without success because it is not possible to get the count() - no persistent class for Integer.class.
Any help? Do I have to go below to JDBC connection and lost the independency of DB?
Thanks a lot in advance,
Karel
Login
FAQ
Search
