Hibernate version:
3.2.1

Hi,

Since I cannot find anything about how to achieve my goal in the documentation, I am trying to forums....

So, the context of the problem :
in pure SQL, I have something like :
select * from city where gis_distance(..) <= 50000 ORDER BY gis_distance(...);

gis_distance is a DMBS-specific function, and (..) are its parameters.

So, I am trying to model that as a Criteria...
the WHERE gis_distance(...) <= 50000 is easy to translate, using a Restrictions.sqlRestrction().

However, I do not see any Order.sqlOrder method in the Order class...


Another attempt at solving the problem has been to consider the following SQL query :
select *, gis_distance(..) AS d where gis_distance(..) <= 50000 ORDER BY d;

And then, I can use a Projections.sqlProjection() with an alias of "d" to create a projection on the gis_distance(..) function, and then .addOrder(Order.asc("d")) on the Criteria.


=> Now, hibernate tells me :
org.hibernate.QueryException: could not resolve property: d of: funala.domain.en
tity.globalization.GisFeature


So, if anyone has a tip, a pointer, or anything that could me move forward, I'd love to hear about him :)

Regards,
Sami Dalouche

Hey,

if anyone wonders, here is a work-around implementation of "Order" that can be used to produce Native SQL Order by clauses.

public class NativeSQLOrder extends Order {
private static final long serialVersionUID = 1L;
private final static String PROPERTY_NAME = "uselessAnyways";
private boolean ascending;
private String sql;

protected NativeSQLOrder(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));
}

}

criteria.addOrder(new Order("whatever SQL using {alias}", true));