I'm trying to select and order by collection size. After two hours of trying to use the examples at [1], I thought i should turn in for help :-)

Consider a class House with a set of residents like :

<class name="House" table="HOUSES">
<!-- ... -->
<set name="residents" table="house_residents"
lazy="true" inverse="true"
cascade="none" order-by="something">
<key column="house"/>
<one-to-many class="Resident"/>
</set>
</class>

I tried stuff like

select house
from House house
left join house.residents resident
group by house
order by count(resident ) desc

but I keep getting "Invalid use of group function"...

any pointers much appreciated :-)

Has anyone managed to order by the size of a collection usin gMySQL 4+? If yes, can I please have an example?

Thanks,

Manos

you could try something like

session.createSQLQuery("select {house.*} from houses as {house} left join house_residents r on ({house}.id = r.house) group by {house}.id order by count(r.id)","house", House.class);

Hi,

Still get a JDBCException following your example:

Caused by: java.sql.SQLException: General error: Invalid use of group function

with

Query qReferers = sess.createSQLQuery(
"select {refHost.*} from HOSTS as {refHost} " +
"left join SESSIONS sess on ({refHost}.id = sess.referingHost ) "+
"group by {refHost}.id order by count(sess.id) LIMIT 30 ",
aliases,
classes );

Any help appreciated,

Manos

Try to get it right with plain SQL first.

Unfortunatelly

SELECT HOSTS.id
FROM HOSTS
LEFT JOIN SESSIONS ON ( HOSTS.id = SESSIONS.referingHost )
GROUP BY HOSTS.id
ORDER BY
count( SESSIONS.id )


works just fine in MSSQL but not in MySQL, which is my target DB. I just need a workaround to be able to sort based on collection size in MySQL and was hoping someone has already such a solution and is willing to share it and save my a$$ here ;-)

Thanks,

Manos

After googling a little more, I found out MySQL needs an alias for count() in the order by clause to work :

SELECT HOSTS.*, count( SESSIONS.id ) as counted FROM HOSTS LEFT JOIN SESSIONS ON ( HOSTS.id = SESSIONS.referingHost ) GROUP BY HOSTS.id ORDER BY counted;

Which brings us to:

Query qReferers = sess.createSQLQuery("select {refHost}.*, count(sess.id) as counted from HOSTS as {refHost} left join SESSIONS sess on ({refHost}.id = sess.referingHost ) group by {refHost}.id order by counted LIMIT 30 ", aliases,classes );

Can this be mad eto work directly in the MySQL dialect? Using createSQLQuery solves the issue but shoots down portability etc...

Does anybody knows why the alias for the count(*) does not work?
/rob