hi there,
I am trying to write the following SQL query:
SELECT table.column1, table.column2, alias1.column2, alias2.column2
FROM table
LEFT JOIN table AS alias1 ON table.id=alias1.id
LEFT JOIN table AS alias2 ON alias1.id=alias2.id
WHERE table.column3='xyz'
AND alias1.colum3='aaa'
AND alias2.colum3='aab'
I have managed most of this using criteria:
Criteria crit=new Criteria();
//add aliases
crit.addAlias("alias1", TablePeer.TABLE_NAME);
crit.addAlias("alias2", TablePeer.TABLE_NAME);
//add joins
crit.addJoin("alias1.ID", TablePeer.ID);
crit.addJoin("alias2.ID", "alias1.ID");
//add search criteria
crit.add(TablePeer.COLUMN_3, 'xyz');
crit.add("alias1.COLUMN_3", 'aaa');
crit.add("alias2.COLUMN_3", 'aab');
//add select by column
crit.addSelectColumn(TablePeer.COLUMN_1);
crit.addSelectColumn(TablePeer.COLUMN_2);
crit.addSelectColumn("alias1.COLUMN_2");
crit.addSelectColumn("alias2.COLUMN_2");
List results=TablePeer.doSelect(crit);
The correct SQL query is output to the Torque log.
However, I am puzzled as to how to select the different result objects
that result from the query. Whilst iterating through the List of
results, I have tried:
* casting the result object to com.workingdogs.village.Record, as
suggested in another post; this threw a ClassCastException
* casting as a Table object, then using getByName("alias1.COLUMN_X") (or
getByName(TablePeer.COLUMN_1) if appropriate) to get the relevant
column; this returns a null result for both the aliased tables and the
original table
* using getByPosition(int); as specified in the docs, this only works
for the first two columns in by example.
So how do i get the record objects out of my list of results?
thanks,
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
