7.14. Tips & Tricks

To order a result by the size of a collection, use the following query:

Copy to Clipboard Toggle word wrap

select usr.id, usr.name
from User as usr 
    left join usr.messages as msg
group by usr.id, usr.name
order by count(msg)

select usr.id, usr.name
from User as usr 
    left join usr.messages as msg
group by usr.id, usr.name
order by count(msg)

If your database supports subselects, you can place a condition upon selection size in the where clause of your query:

Copy to Clipboard Toggle word wrap

from User usr where size(usr.messages) >= 1

from User usr where size(usr.messages) >= 1

If your database doesn't support subselects, use the following query:

Copy to Clipboard Toggle word wrap

select usr.id, usr.name
from User usr.name
    join usr.messages msg
group by usr.id, usr.name
having count(msg) >= 1

select usr.id, usr.name
from User usr.name
    join usr.messages msg
group by usr.id, usr.name
having count(msg) >= 1

As this solution can't return a User with zero messages because of the inner join, the following form is also useful:

Copy to Clipboard Toggle word wrap

select usr.id, usr.name
from User as usr
    left join usr.messages as msg
group by usr.id, usr.name
having count(msg) = 0

select usr.id, usr.name
from User as usr
    left join usr.messages as msg
group by usr.id, usr.name
having count(msg) = 0