How to express this SQL in Hibernate Criteria (Possible?)

Question

Please take a look at this SO answer:

MySQL get row position in ORDER BY

Basically, getting the position of a row when there is an order by clause. Is it possible to express this in Hibernate criteria?

Here is the query as I have it. What I'm really looking for is an elegant way to set dynamic where clause params:

SELECT x.id, 
       x.position
  FROM (SELECT dwq.id,
               @rownum := @rownum + 1 AS position
          FROM default_work_queue dwq
          JOIN (SELECT @rownum := 0) r 
          WHERE dwq.type=4 *AND FOO=BAR*
      ORDER BY dwq.description ASC) x
 WHERE x.id=540;

That is, I want to be able to add 0-n number of extra 'and foo=bar' to the noted part of the quere, without doing a bunch of string manipulation.

Answer 1

it is not possible with criteria because criteria does not support arbitrary sql in the FROM-clause. However here's another idea for this query

// select the count of all elements before the wanted element
int position = session.createCriteria(Work.class)
    .add(restrictions)
    .add(Subqueries.lt("description", DetachedCriteria.For(Work.class)
        .add(Restriction.eq("id", id))
        .setProjection(Projection.Property("description"))))
    .setProjection(Projection.RowCout())
    .UniqueResult<int>();

you could optimise the subquery away if you have the description already loaded