Apache Torque Criteria: how to join a subquery

Question

Let's say I have the follow SQL statement. I would like to convert this to Torque Criteria:

SELECT table.name, subq1.total AS 'TOTAL', subq2.total2 AS 'SECOND TOTAL'
FROM table
LEFT JOIN (
    SELECT c.login, COUNT(*) AS 'total'
    FROM table2 INNER JOIN table3
    WHERE table3.field = 2
    GROUP BY table3.login
    ) AS subq1 ON(subq1.login = table.login)
LEFT JOIN(...) AS subq2 ON (subq2.login = table.login)

It does not matter the subquery itself. The only issue here is how to perform that LEFT JOINs.

score 2 · Answer 1 · answered Jan 12 '11 at 00:25

Basically, I don't think torque Criteria is suited to this type of query. First off you're selecting specific columns. Criteria is generally used for selecting torque objects for a queried table. You can select specific columns using village records though, so it is actually possible to select custom column using criteria, but cumbersome*. Second, and most important, I don't believe a LEFT join is possible. Criteria is set up to use JOINs basically as a subquery AFAIK.

A sample subquery using a basic criteria "join" would be

Criteria criteria = new Criteria();
criteria.add(TABLEA.COLUMNA,somevalue);
criteria.add(TABLEB.COLUMNA,somevalue);
criteria.addJoin(TABLEA.COLUMNB,TABLEB.COLUMNB);
TABLEA.doSelect(criteria);

This would select entries from table A where Table B column a = somevalue and table a column b = table b column b.

All in all I would just recommend a direct query for overly complex criteria.

public static List<Object> doDynamicQuery(String dynamicQuery){
  Connection connection = null;
  try{
   connection = Torque.getConnection(Torque.getDefaultDB());
   connection.setReadOnly(true);
   PreparedStatement statement = connection.prepareStatement(dynamicQuery);
   ResultSet set = statement.executeQuery();
   QueryDataSet dataSet = new QueryDataSet(set);
   return BasePeer.getSelectResults(dataSet);
  }
  catch(Exception e){
   log.error(e);
   return null;
  }
  finally{
   Torque.closeConnection(connection);
  }
 }

http://www.jajakarta.org/turbine/jp/turbine/torque/peers-howto.html Check these two links out for some helpful pointers. — Sheldon Ross, Jan 12 '11 at 00:32
Agreed. I also would use direct query. But this is a project requirement, and I can't make exceptions (only if there's no other way). About the specific columns you've mentioned, sorry if I didn't write that right, they ARE torque objects. I'll fix it now. And thank you about the basic examples and links, but that was not actually the question. Left Joins ARE possible with torque's criteria. The really problem here is to JOIN (or LEFT JOIN, whatever) a subquery based on an specific column of the subquery (in the example above, subq1.login). — bluefoot, Jan 12 '11 at 12:25
The Example of a simple join I provided doesn't work? criteria.addJoin(Subq1Peer.LOGIN,TablePeer.LOGIN)? — Sheldon Ross, Jan 12 '11 at 19:38

score 0 · Answer 2 · answered Jun 06 '14 at 13:28

You could do it by explicitely defining mappers and selected columns:

Consider

crit.addSelectColumn( MyTablePeer.COL1);
Collections.addAll( crit.getSelectColumns(), MyTable2Peer.getTableMap().getColumns()  );
crit.addJoin(MyTable2Peer.ID, MyTablePeer.ID2, Criteria.LEFT_JOIN);
//crit.where(...)
CompositeMapper cp = new CompositeMapper();
cp.addMapper( new IntegerMapper(), 0 ); // if you expect an int value
cp.addMapper( new BaseMyTable2RecordMapper(),1);
List<List<Object>> resultList = MyTable2Peer.doSelect( crit, cp );
// resultList.get(0).get( 1 ) instanceof MyTable2)

using Torque automatically mapping mechanism. There also exists an ObjectListMapper..

bluefoot · Accepted Answer · 2011-03-15T13:43:41.277

I ended up splitting every subquery in a separated method. But I could also have used Criterion. Something like:

Criterion criterion = myCriteria.getCriterion(MyTablePeer.STARTINGDATE);

Criterion c1 = myCriteria.getNewCriterion(criterion.getTable(), 
        criterion.getColumn(), 
        "something", Criteria.LESS_THAN);
c1.and(myCriteria.getNewCriterion(criterion.getTable(), 
        criterion.getColumn(),
        someDate, Criteria.GREATER_THAN));
criterion.or(c1);
myCriteria.add(criterion);

so the idea is: every criterion is a subquery. and you can put "or" or "and" or whatever, and in the end, join the criterion with the main criteria.

score 0 · Answer 4 · answered May 03 '11 at 16:36

0

Create a new view in the database implementing your complex query, then a read-only Torque OM class you can query trivially from your application.

answered May 03 '11 at 16:36

ewg

319
1
3

it's one possible way. But the application will become dependent on database, and business logic split within app and database. – bluefoot May 04 '11 at 11:11

Apache Torque Criteria: how to join a subquery

4 Answers4