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.

Answer 1

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.