Dear All,

I'm struggling with converting PgSQL querry 2 HQL querry (Hibernate 3.3.0):

select experiment.*, count(artifactholder.*)
from experiment left outer join artifactholder on artifactholder.experiment_fk = experiment.id
where experiment.research_fk = 1
group by experiment.id
having count(artifactholder.*) < experiment.targettrialscount

Entities setup:
Research have set of experiments,
each experiment have set of trials (artifactholders) and property targetTrialsCount

SQL query returns all experiments which have smaller amount of related trials than specified by experiment.targetTrialsCount property.

I've tried different combinations of left/right outer joins, from both ends Experiment/Trial (artifactholder), but no luck.
Here is latest version (which still is does not includes HAVING clause):

select
ejoin, count(ejoin)
from
Trial as t
left outer join t.experiment as ejoin
where
ejoin.research = :research
group by
ejoin.id

Problem with this HQL is that it does not returns experiments with zero trials (artifactholders).

Any suggestions?

Thanks in advance.

Why not using native SQL Query Api (hibernatesession.createSQL("sql")) instead of using HQL ?
Using native SQL Query Api you can reuse exactly the same sql
as you build and run directly on the database (for example in a query-tool).

> Why not using native SQL Query Api: well, it is an option, but I'm afraid of some differences between HSQLDB and Postgres.

Solved it in HQL:

SELECT e FROM Experiment as e WHERE size(e.trials) < e.targetTrialsCount AND e.research = :research

Still if you have any doubts, clear here....

Hibernate Fulll tutorials