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>
Received-SPF: pass (nike.apache.org: domain of monroe@dukece.com designates
 67.202.195.198 as permitted sender)
From: Greg Monroe <monroe@dukece.com>
To: 'Apache Torque Users List' <torque-user@db.apache.org>
Date: Mon, 2 Mar 2009 12:43:56 -0500
Subject: RE: Multiple addJoin with OR
Thread-Topic: Multiple addJoin with OR
Thread-Index: AcmbPmHt3qkASuM2TQebHeUSc5PqlgAAO29QAAa6P5A=
Message-ID: 
 <55B8568AFA5F144F886F18186BAD5B2C054DF30038@durham-e2k7mb1.dukece.com>
References: <002101c99b37$3cdc31e0$b69495a0$@com>
 <OF93F675E9.2B4FDFE3-ONC125756D.0049837E-C125756D.0049DD72@seitenbau.net>
 <002a01c99b3b$fb1876b0$f1496410$@com>
 <OF1BE602F1.3C0C88CF-ONC125756D.004BD922-C125756D.004C5567@seitenbau.net>
 <003101c99b3f$78d500c0$6a7f0240$@com>
In-Reply-To: <003101c99b3f$78d500c0$6a7f0240$@com>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"

You might be able to do this using custom criteria instead=0D=0Aof a join=
=2E See details in the "Reading from the DB" section=0D=0Aof the Runtime Re=
ference=2E  I'd suggest constructing the=0D=0Astring using the Peer static =
variables for greatest protection=0D=0Afrom table modification=2E=0D=0A=0D=
=0AYou may also need to add the extra tables via an addAlias =0D=0Aor other=
 method as well=2E=0D=0A=0D=0AAs an aside, you might want to consider the l=
ong term =0D=0A"readability / maintenance" side of design=2E CPU cycles =0D=
=0Aare no longer precious items to be miserly with by =0D=0Adeveloping comp=
lex single queries, which often come back=0D=0Ato bite you when data condit=
ion not tested for are=0D=0Aencountered=2E  =0D=0A=0D=0ADoing things with e=
asily understandable multiple queries =0D=0Aoften doesn't take more time th=
an a single complex query =0D=0Athat doesn't have indices optimized for it=
=2E  And if =0D=0Afuture changes or fixes are needed, they can be done =0D=
=0Afaster since the logic is clear=2E=0D=0A=0D=0AOf course, that depends on=
 your needs=2E e=2Eg=2E, this query is =0D=0Abeing called VERY frequently (=
e=2Eg=2E multiple times on a home=0D=0Apage) then go for it=2E =0D=0A=0D=0A=
> -----Original Message-----=0D=0A> From: Ludwig Magnusson [mailto:ludwig@i=
tcatapult=2Ecom]=0D=0A> Sent: Monday, March 02, 2009 9:02 AM=0D=0A> To: 'Ap=
ache Torque Users List'=0D=0A> Subject: RE: Multiple addJoin with OR=0D=0A>=
 =0D=0A> =0D=0A> -----Original Message-----=0D=0A> From: Thomas Fischer [ma=
ilto:fischer@seitenbau=2Enet]=0D=0A> Sent: den 2 mars 2009 14:54=0D=0A> To:=
 Apache Torque Users List=0D=0A> Subject: RE: Multiple addJoin with OR=0D=
=0A> =0D=0A> > > I want to create an SQLQuery that combines data from three=
 tables=2E=0D=0A> Right=0D=0A> > > now, my javacode looks like this:=0D=0A>=
 > >=0D=0A> > > Criteria=2EaddJoin(TableA=2Eid, TableB=2Efk);=0D=0A> > > Cr=
iteria=2EaddJoin(TableA=2Eid, TableC=2Efk);=0D=0A> > >=0D=0A> > > This resu=
lts in this SQL query:=0D=0A> > >=0D=0A> > > WHERE TableA=2Eid =3D TableB=
=2Efk=0D=0A> > >=0D=0A> > > AND TableA=2Eid =3D TableC=2Efk=0D=0A> > >=0D=
=0A> > > However, this is not the result I need=2E What I need is a query w=
ith=0D=0A> OR=0D=0A> > > instead of AND, like this:=0D=0A> > >=0D=0A> > > W=
HERE TableA=2Eid =3D TableB=2Efk=0D=0A> > >=0D=0A> > > OR TableA=2Eid =3D T=
ableC=2Efk=0D=0A> > >=0D=0A> > > I haven't found any way to do this using c=
riteria=2E Is it possible?=0D=0A> >=0D=0A> > I do not know a way to do this=
 currently=2E But are you really sure your=0D=0A> > statement makes sense ?=
 If one row in table B matches, then you get all=0D=0A> > rows of table C j=
oined (or vice versa), and I cannot imagine this is=0D=0A> what=0D=0A> > yo=
u need=2E But I might be wrong=2E=0D=0A> >=0D=0A> >      Thomas=0D=0A> >=0D=
=0A> > It does make sense=2E I rewrote the query by hand (changed AND to OR=
) and=0D=0A> > executed it in the MySql console and it gave the response I =
wanted=2E=0D=0A> >=0D=0A> > The situation is kind of like this:=0D=0A> > Ta=
ble a is a category table, it only contains ids and names of=0D=0A> categor=
ies=2E=0D=0A> > Table B is a "products sold" table, and table C is a "produ=
cts bought"=0D=0A> > table=2E All products belong to a certain category, an=
d I want a query=0D=0A> that=0D=0A> > gives me all the categories that a ce=
rtain user has sold/bought=0D=0A> products=0D=0A> > from=2E=0D=0A> >=0D=0A>=
 > /Ludwig=0D=0A> >=0D=0A> =0D=0A> Ok, you have additional constraints on B=
 and C; then this makes more=0D=0A> sense=2E=0D=0A> What you can do is to s=
plit this to 3 queries:=0D=0A> - Select the categories from A=0D=0A> - add =
the categories from B=0D=0A> - Do a select on the products table with the r=
esult=2E=0D=0A> The only performance disadvantage is that you have 3 querie=
s=2E=0D=0A> =0D=0A> Alternatively you could reformulate your query using su=
bselects, which is=0D=0A> supported in Torque 3=2E3=2E=0D=0A> =0D=0A>     T=
homas=0D=0A> =0D=0A> Yes, the problem is always solveable, but it would be =
nice to be able to=0D=0A> do=0D=0A> it in one criteria=2E I will use a temp=
orary solution for now however=2E=0D=0A> /Ludwig=0D=0A> =0D=0A> =0D=0A> ---=
------------------------------------------------------------------=0D=0A> T=
o unsubscribe, e-mail: torque-user-unsubscribe@db=2Eapache=2Eorg=0D=0A> For=
 additional commands, e-mail: torque-user-help@db=2Eapache=2Eorg=0D=0A> =0D=
=0A=0D=0ADukeCE Privacy Statement:=0D=0APlease be advised that this e-mail =
and any files transmitted with=0Ait are confidential communication or may o=
therwise be privileged or=0Aconfidential and are intended solely for the in=
dividual or entity=0Ato whom they are addressed=2E If you are not the inten=
ded recipient=0Ayou may not rely on the contents of this email or any attac=
hments,=0Aand we ask that you please not read, copy or retransmit this=0Aco=
mmunication, but reply to the sender and destroy the email, its=0Acontents,=
 and all copies thereof immediately=2E Any unauthorized=0Adissemination, di=
stribution or copying of this communication is=0Astrictly prohibited=2E

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