Re: ORDER BY and torque generated column names

Hi Graham,
Try using the addSelectColumn() method on APeer class, and BPeer class.
Regards,
Malcolm Kendall, NetVisionIT Ltd.

Graham Leggett wrote:
> Hi all,
>
> I am struggling with a database query that is giving an error about 
> the presence of specific column names, and as torque takes control 
> over the column names I am in the dark as to what torque is or isn't 
> doing.
>
> The query generated by torque attempts to order results by two 
> columns, like this:
>
> SELECT DISTINCT  FROM a, b WHERE a.policy_id=b.policy_id ORDER BY 
> b.underwriting_year DESC, a.policydetail_id DESC;
>
> Being printed out from a Criteria object, the column names are 
> missing, as torque fills these in for you.
>
> Postgres fails on this query with the error "ERROR: for SELECT 
> DISTINCT, ORDER BY expressions must appear in select list"
>
> The trouble is, I do not know what the select list is, because torque 
> fills that bit in during the query - I have no mechanism (that I know 
> of) to see the final query that hits the database.
>
> If I use a select list of "*" (as in "SELECT DISTINCT * FROM...") the 
> query works and postgres is happy, so the query itself seems fine, it 
> seems that torque is leaving out the columns specified in orderby.
>
> Does torque give the user any control over the columns being selected 
> in the query?
>
> Am I doing something wrong in the following code?
>
> criteria.addJoin(BPeer.POLICYDETAIL_ID, APeer.POLICYDETAIL_ID);
> criteria.addDescendingOrderByColumn(BPeer.UNDERWRITING_YEAR);
> criteria.addDescendingOrderByColumn(APeer.POLICYDETAIL_ID);
> APeer.doSelect(criteria);
>
> Regards,
> Graham
> --