CODEWITHSUNDEEP
javahibernatespring-mvchql
AlyoshaKaramazov
AlyoshaKaramazov

Reputation: 616

Order the result of query by collection size in HQL

I'm trying to return the result of a HQL query as follows:

List<Parent> parents = 
currentSession().createQuery("from Parent order by count(children)").list();

So that the list is ordered by the bumber of children eadh parent has,
where children is an ArrayList<Child>.

I get the following error:

HTTP Status 500 - Request processing failed;
nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: Unknown column 'qualified' in 'order clause';
SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: Unknown column 'qualified' in 'order clause'

It seems quite a simple query and exactly what the count method is for so I don’t really understand why it doesn't work. Also I don't know where the column 'qualified' is coming from or what it means.

Upvotes: 2

Views: 2505

Answers (2)

杨家勇
杨家勇

Reputation: 53

I also encountered the problem like this.When i search in google for solution, i see your question in statckoverflow,and now you can reference with my case.

Class AEntity{

    @OneToMany
    Set<BEntity> bList;

}

HQL:from AEntity order by bList.size

Hope to help somebody like us.Thank you.

Upvotes: 1

AlyoshaKaramazov
AlyoshaKaramazov

Reputation: 616

In this case it's pretty much standard SQL as I see it, so

"from Parent p left join p.children as pc group by p order by count(pc)"

is what I needed, however this gave an error due to it now returning tuples with an extra column, the number of children. This was fixed by specifying in the select clause that I wanted to return only the parent part of the joined table. I'm not exactly sure what was going on under the hood here but this works and make a nice logical and readable query. 

"select p from Parent p left join p.children as pc group by p order by count(pc)"

simples.

Upvotes: 0

Related Questions