I am trying (unsuccessfully) to convince Hibernate to produce a query with an extra condition added to the "ON" part of the outer join instead of the "WHERE" clause, like this:

SELECT
p.id, count(c.id)
FROM
parent p
left outer join child c ON c.pid=p.id and c.code='y'
GROUP BY p.id

I've tried changing my HQL and adding a "where" attribute to my mapping, but I always get this SQL generated:

SELECT
p.id, count(c.id)
FROM
parent p
left outer join child c ON c.pid=p.id
WHERE
c.code='y'
GROUP BY p.id

The difference is that the second statement does not return some of the rows that I need. Here is my test data (oversimplified to fit this page :-)

create table parent (id integer);
create table child (id integer,pid integer,code char(1));
insert into parent (id) values (1);
insert into parent (id) values (2);
insert into child (id,pid,code) values (1,1,'n');
insert into child (id,pid,code) values (6,2,'y');
insert into child (id,pid,code) values (7,2,'n');

The query I can generate returns
(2,1);
The query I need would return
(1,0)
(2,1).

Does anyone know a trick I can use to push my condition into the ON clause? Should I post a feature request to JIRA instead?

Thanks in advance,

Sergey


Hibernate version: 2
Name and version of the database you are using: MySQL

Filters are the only way to get snippets added to the ON clause, and filters are only available in Hibernate3.