Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Date: Mon, 28 Jul 2003 19:14:59 +0100
Message-Id: <200307281814.h6SIExo26122@www.networkphotographers.com>
From: "Elie Medeiros" <nc-ml@networkphotographers.com>
To: "Weber, Nadja" <Nadja.Weber@t-systems.com>
CC: torque-user@db.apache.org
Subject: Re: AW: self joins in Torque
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1

hi there,

yes, i found a work-around but it's not pretty:

Criteria crit=new Criteria();
crit.addAlias("table_name_1", TablePeer.TABLE_NAME);
crit.addAlias("table_name_2", TablePeer.TABLE_NAME);
crit.addJoin("table_name_1.primary_key_value", TablePeer.PRIMARY_KEY_VALUE);
crit.addJoin("table_name_2.primary_key_value", TablePeer.PRIMARY_KEY_VALUE);
crit.add(TablePeer.RESULT, "test");
crit.add("table_name_1.result", "test again");
crit.add("table_name_2.result", "test three times");
ArrayList results=(ArrayList)TablePeer.doSelect(crit);

Kind of defeats the whole DB abstraction thing a little if you're
entering that stuff as Strings - but it works...

hope this helps

Elie

> hi,
> 
> did you get any answers to your question? i also face the problem of
making self-joins in torque. so if you have any advice, it would be
highly appreciated.
> 
> regards
> nadja weber
> 
> -----Urspr�ngliche Nachricht-----
> Von: Elie Medeiros [mailto:nc-ml@networkphotographers.com]
> Gesendet: Mittwoch, 25. Juni 2003 10:47
> An: Turbine Torque Users List
> Betreff: self joins in Torque
> 
> 
> (sorry for double-post, got wrong subject in previous email)
> 
> 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: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org