hi there, I've been looking to do a query with several self-joins in Torque and haven't found much about how to do it, other than it is possible according to the doc, and that the person who wrote that bit of the doc has some "very efficient code" to do so (but doesn't mention what that code is - point 4 in the intro to http://db.apache.org/torque/peers-howto.html).
The query in SQL would look something like so: SELECT sr1.key, sr2.key, sr3.key from results as sr1 INNER JOIN results AS sr2 ON sr2.key=sr1.key INNER JOIN results as sr3 ON sr3.key=sr2.key WHERE sr1.result_set='001' AND sr2.result_set= '015' AND sr3.result_set='098' where the table definition looks something like: ------------------------------ |id | key | result_set | ------------------------------ the idea is to get a set of keys which are contained in several result_sets at a time. I tried using Criterions as follows: --- Criteria crit = new Criteria(); Criteria.Criterion tg1 = crit.getNewCriterion(ResultsPeer.RESULT_SET, '001', Criteria.EQUAL); Criteria.Criterion tg2 = crit.getNewCriterion(ResultsPeer.RESULT_SET, '015', Criteria.EQUAL); Criteria.Criterion tg3 = crit.getNewCriterion(ResultsPeer.RESULT_SET, '098', Criteria.EQUAL); crit.add(tg1.and(tg2.and(tg3))); --- this doesn't work obviously - I'm not using joins. At the moment I use OR as in (tg1.or(tg2.or(tg3))) and loop through the results in Java, this is quite inefficient and a little ugly though. How would I add joins to the Criteria and tell tg1 to relate to join1, tg2 to relate to join 2 etc...? Any help greatly appreciated.... Nick ps. if the person who wrote that post in the doc is still on the mailing list, I for one am rather interested in his code to load a tree from the db in a single hit :D --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
