Hi ,

I need to do order by a column of child table.I tried with the following code, but it's not giving me the desired result.

I have two tables ACCOUNT_FILTER(parent) and ACCOUNT_CONFIG_FILTER(child) and am trying to do the asc order on the column 'GENRE' of child table.

Criteria criteria = getSession().createCriteria(AccountFilter.class);
criteria.add(Restrictions.eq("id",accountFilterId));
criteria.createAlias("accountConfigurationFilters", "acf");
criteria.addOrder(Order.asc("acf.genre"));

Per the above code , I should get the 'genre' values in the alphabetical order. But I am getting in the following order.

Reggae
Holiday
Pop
Comedy
R B
Chillout
Rock
Miscellaneous
Samba
New Age
null
World Music
Rap/Hip Hop
Metal
Soundtrack
Musical
Dance
Latin
Alternative
Adult Alternative
Adult Contemporary
Alternative Rock
Electronica

Someone please tell me, how to do the order by columns of child table. anything wrong with my code?

create a new criteria on the associated entity, and return your results from that one, not from the main query

Diaz,

Thanks for quick reply. I tried as you suggested, but no luck. Still I am getting the values in random order.

Below query that is generated by the Hibernate looks fine for the below code . It's doing the order by genre ASC. I am not sure why I am not getting the ordered values in my Java class.

Criteria criteria = getSession().createCriteria(AccountFilter.class);
criteria.add(Restrictions.eq("id",accountFilterId));
criteria.createAlias("accountConfigurationFilters", "acf");
criteria.addOrder(Order.asc("acf.genre"));

SELECT this_.account_filter_id AS account1_13_1_,
acf1_.major_label AS major5_14_0_, acf1_.label AS label14_0_,
acf1_.genre AS genre14_0_, ,
acf1_.account_filter_id AS account25_14_0_
FROM dmd.account_filter this_ INNER JOIN dmd.account_configuration_filter acf1_
ON this_.account_filter_id = acf1_.account_filter_id
WHERE this_.account_filter_id = ?
ORDER BY acf1_.genre ASC


If I put the criteria conditions i.e like, or, and etc on child table columns then I am getting the results fine. But. order by on the child columns is not working with criteria.createCriteria OR criteria.createAlias.


Help pleaseeeeeeee..

Here I am trying to fetch the parent object and the corresponding child objects (one-to-many association) order by on the child table column.

I tried with following code too.

List<AccountFilter> ac = (List<AccountFilter>) getSession().createCriteria(AccountFilter.class)
.add(Restrictions.eq("id",accountFilterId))
.createCriteria("accountConfigurationFilters","acf")
.addOrder(Order.asc("acf.genre")).list();

when I do, ac.getChildRecords(), they are not coming in asc order of 'genre' column.


Is it possible to get the child records order by a specific column ?

It's not possible to order by a column on the child table. Find more in the below link

http://forum.hibernate.org/viewtopic.ph ... 187428d902