Ordering by an expression with Criteria API?

rmann · **Joined:** Sat Jan 06, 2007 3:46 pm **Posts:** 15

Hi. I have a table of events with a date column. I'd like to fetch all the rows in the table, and order them like this (in MySQL):

Code:

order by event_date < now(), event_date

So that the next event to occur is at the top, followed by the one after that, etc, and then followed after that by past events.

I don't think the Order API lets me specify an expression to order by, as it only accepts property names. I'm wondering if there's a way to do this that isn't obvious in the docs. Thanks!

mmerder · **Posted:** Tue Nov 03, 2009 9:53 am

you need to subclass Order:

Code:

public class SQLOrder extends Order {

    private static final long serialVersionUID = 1L;
    private final static String PROPERTY_NAME = "uselessAnyways";
    private boolean ascending;
    private String sql;

    protected SQLOrder(String sql, boolean ascending) {
        super(PROPERTY_NAME, ascending);
        this.sql = sql;
        this.ascending = ascending;

    }

    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
            throws HibernateException {
        StringBuilder fragment = new StringBuilder();
        fragment.append("(");
        fragment.append(sql);
        fragment.append(")");
        fragment.append(ascending ? " asc" : " desc");
        return StringHelper.replace(fragment.toString(), "{alias}",
                criteriaQuery.getSQLAlias(criteria));
    }

}

now you can use it when assembling your criteria.

fitech · **Joined:** Wed Jul 30, 2008 8:43 am **Posts:** 32

Hi.

can the same be done for HSQL? I need two things:

1. possibility to order by alias:

Code:

select ord, (select sum(pays.income) from ord.payments pays) as sumIncome from Order as ord order by sumIncome

2. possibility to add NULLS FIRST/NULLS LAST

rmann · **Joined:** Sat Jan 06, 2007 3:46 pm **Posts:** 15

mmerder wrote:

you need to subclass Order:
[/code]

now you can use it when assembling your criteria.

Thanks!