Re: failure notice

[Elie Medeiros <nc...@networkphotographers.com>]

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