Custom SQL for ORDER BY

[Jonathan Purvis <jo...@reeltwo.com>]

Is it possible to use an SQL formula for the ORDER BY column with 
Torque?  I was looking generated SQL something like this:

SELECT * FROM rules WHERE rules.type_id=3 ORDER BY abs(rules.year-2003) 
LIMIT 1

(where the 3 and 2003 come from variables).  This would give me the type 
3 Rule object for the year closest to 2003.

I can simulate this by putting the ORDER BY clause in a custom SQL 
Criteria entry, but that's a really ugly way of doing it.


Thanks,

	Jon

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

RE: Custom SQL for ORDER BY

[Thomas Fischer <fi...@seitenbau.net>]

Hi,

Torque does not know about SQL functions. You have to insert them by hand,
I'm afraid.
The following should do in 3.2-RC1:

criteria.add(RulesPeer.TYPE,3);
criteria.addAscendingOrderBy("abs(" + RulesPeer.YEAR + " - 2003)");

(functions in orderBys are not supported in 3.1.1, as far as I remember.
There should be an enhancement request in scarab.)

    Thomas

Jonathan Purvis <jo...@reeltwo.com> schrieb am 24.08.2005 05:00:20:

> Is it possible to use an SQL formula for the ORDER BY column with
> Torque?  I was looking generated SQL something like this:
>
> SELECT * FROM rules WHERE rules.type_id=3 ORDER BY abs(rules.year-2003)
> LIMIT 1
>
> (where the 3 and 2003 come from variables).  This would give me the type
> 3 Rule object for the year closest to 2003.
>
> I can simulate this by putting the ORDER BY clause in a custom SQL
> Criteria entry, but that's a really ugly way of doing it.
>
>
> Thanks,
>
>    Jon
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org