Some difficulties with this HQL query

aircom · **Joined:** Sat Nov 29, 2008 6:30 pm **Posts:** 8

Hi,

I guess I would have the same problem with SQL, I'm a beginner for both. Anyway, here is a HQL query that seems to give me the right result:

Code:

@"select Parent
    from Parent parent
        join parent.Children ch
        where (ch.Type = :chType) and
              (select count(*) from parent.Children) = 1")

What I want with that is get all parents that have only one entry in the Children table and this single entry has a particular value for a field.

What I'm not sure is if this is the right way to achieve what I want. Is it performant? In my version, is the count(*) triggered for each child of a parent (so if there is N children, the same count would be triggered N time)?

Thanks for your help.

Nicolas

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

To see if your query is good or bad in terms of execution time and plan you would have to take the time it takes to execute and if the execution plan for the generated SQL is bad in any way.

I personally would write it like this:

Code:

from Parent parent join parent.children ch where ch.type = :chType group by parent having count(parent.children)=1

You don't need the part before "from" if you only want the main object (parent in this case)
If i'm not mistaken the property names (ch.Type, parent.Children) should be in lowercase but if it works that way too it's ok i guess.

Hope that helps, rating appreciated

aircom · **Joined:** Sat Nov 29, 2008 6:30 pm **Posts:** 8

With this query I get an exception:

Code:

unindexed collection before [] [from Parent parent
                       join parent.Children ch
                       where (ch.Type = :chType)
                       group by parent
                       having count(parent.Children) = 1]

I should add that "Children" is a Set if that matters.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

try just count(ch) or count(ch.id) (or whatever the id is called) instead of count(parent.Children)

aircom · **Joined:** Sat Nov 29, 2008 6:30 pm **Posts:** 8

No luck, I have now:

Column 'Parents.State' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

State is one of the columns of my Parents table.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

phew... Although i'm pretty sure there's an easier way you could try add all the parents properties to the group by clause

mmerder · **Posted:** Tue Jan 27, 2009 3:24 am

Yes, pkleindl is right, you have to include all selected (non-aggregated) properties (event though on some databases your query works). This has to be done until HHH-1615 is fixed.

BTW: In HQL you can also use size() to get a collections size:

Code:

from Parent p where size(p.children) = 1

or in your case:

Code:

from Parent parent join parent.children ch where ch.type = :chType group by [parent's properties] having size(parent.children)=1

aircom · **Joined:** Sat Nov 29, 2008 6:30 pm **Posts:** 8

With these changes I still get the "unindexed collection before []" error.
Anyway, I will stick to the first version for the moment, using size() which will shorten the query a bit. Thanks to all.