Hibernate version:2.1.2
Database: Ingres 2.6 SP2

When you write a HQL query, it is actually possible to drop back to SQL in the ORDER BY clause, like this:


Notice the SQL column name in the ORDER BY clause, but a correct property reference in the SELECT clause.

Unfortunately, the query that we want to do this for is significantly more complex than that query, specifically, lots of tables are involved.

We need to use the SQL alias that HB generates in order to resolve the column name in our ORDER BY clause.

The current way we do this is:
- construct the HQL query without the ORDER BY clause
- run the unit test
- check the SQL generated
- embed the SQL alias into the HQL query in the ORDER BY clause

This is pretty dodgy (not that injecting SQL into the HQL is particularly elegant to begin with), and it causes problems when we modify the HQL.

Is there any way to either force HB to use given SQL aliases, or predict what aliases it will use?

P.S. The reason we need to use SQL is we need to stomp the time portion of the d_created date column so objects created on the same day are considered equal. Does anyone know a way of doing this in HB, other than by using SQL in the HQL for the ORDER BY clause (at the moment, we want to avoid doing the ordering of the result set in Java land if we can).

I feel uncomfortable with what you are doing. Yes hibernate will pass it through if it cannot interpret it but its dodgy alright. Having said that I must not understand why you are finding it necesary to use column directly.
Why can't you use...


Assuming that the field d_created is mapped to Object.getCreatedDate()

Yeah, but as I explained, we need to apply a SQL function to the order by column:


Would it be possible to use that HQL substitutions thing-a-majig to make Hibernate understand ingresSpecificFunctionTheExtractsThings()?