Apache Torque Criteria: how to join a subquery

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 n开发者_运维百科ot matter the subquery itself. The only issue here is how to perform that LEFT JOINs.

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);
  }
 }

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.

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.

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..