You might be able to do this using custom criteria instead of a join. See details in the "Reading from the DB" section of the Runtime Reference. I'd suggest constructing the string using the Peer static variables for greatest protection from table modification.
You may also need to add the extra tables via an addAlias or other method as well. As an aside, you might want to consider the long term "readability / maintenance" side of design. CPU cycles are no longer precious items to be miserly with by developing complex single queries, which often come back to bite you when data condition not tested for are encountered. Doing things with easily understandable multiple queries often doesn't take more time than a single complex query that doesn't have indices optimized for it. And if future changes or fixes are needed, they can be done faster since the logic is clear. Of course, that depends on your needs. e.g., this query is being called VERY frequently (e.g. multiple times on a home page) then go for it. > -----Original Message----- > From: Ludwig Magnusson [mailto:lud...@itcatapult.com] > Sent: Monday, March 02, 2009 9:02 AM > To: 'Apache Torque Users List' > Subject: RE: Multiple addJoin with OR > > > -----Original Message----- > From: Thomas Fischer [mailto:fisc...@seitenbau.net] > Sent: den 2 mars 2009 14:54 > To: Apache Torque Users List > Subject: RE: Multiple addJoin with OR > > > > I want to create an SQLQuery that combines data from three tables. > Right > > > now, my javacode looks like this: > > > > > > Criteria.addJoin(TableA.id, TableB.fk); > > > Criteria.addJoin(TableA.id, TableC.fk); > > > > > > This results in this SQL query: > > > > > > WHERE TableA.id = TableB.fk > > > > > > AND TableA.id = TableC.fk > > > > > > However, this is not the result I need. What I need is a query with > OR > > > instead of AND, like this: > > > > > > WHERE TableA.id = TableB.fk > > > > > > OR TableA.id = TableC.fk > > > > > > I haven't found any way to do this using criteria. Is it possible? > > > > I do not know a way to do this currently. But are you really sure your > > statement makes sense ? If one row in table B matches, then you get all > > rows of table C joined (or vice versa), and I cannot imagine this is > what > > you need. But I might be wrong. > > > > Thomas > > > > It does make sense. I rewrote the query by hand (changed AND to OR) and > > executed it in the MySql console and it gave the response I wanted. > > > > The situation is kind of like this: > > Table a is a category table, it only contains ids and names of > categories. > > Table B is a "products sold" table, and table C is a "products bought" > > table. All products belong to a certain category, and I want a query > that > > gives me all the categories that a certain user has sold/bought > products > > from. > > > > /Ludwig > > > > Ok, you have additional constraints on B and C; then this makes more > sense. > What you can do is to split this to 3 queries: > - Select the categories from A > - add the categories from B > - Do a select on the products table with the result. > The only performance disadvantage is that you have 3 queries. > > Alternatively you could reformulate your query using subselects, which is > supported in Torque 3.3. > > Thomas > > Yes, the problem is always solveable, but it would be nice to be able to > do > it in one criteria. I will use a temporary solution for now however. > /Ludwig > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org > For additional commands, e-mail: torque-user-h...@db.apache.org > DukeCE Privacy Statement: Please be advised that this e-mail and any files transmitted with it are confidential communication or may otherwise be privileged or confidential and are intended solely for the individual or entity to whom they are addressed. If you are not the intended recipient you may not rely on the contents of this email or any attachments, and we ask that you please not read, copy or retransmit this communication, but reply to the sender and destroy the email, its contents, and all copies thereof immediately. Any unauthorized dissemination, distribution or copying of this communication is strictly prohibited. --------------------------------------------------------------------- To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
