RE: Multiple addJoin with OR
Hello again!
Sorry I haven't replied for a while.
I solved the problem however by adding a custom part to the criteria:
criteria.add(CategoryPeer.ID, (Object) String.format("(%s=%s OR %s=%s)",
CategoryPeer.ID, SoldProductPeer.CATEGORY_ID,
CategoryPeer.ID, BoughtProductPeer.CATEGORY_ID),
SqlEnum.CUSTOM);
This gave me the result I wanted. Not perfect, but it works.
/Ludwig
-Original Message-
From: Marc Kannegießer [mailto:mac...@gmx.org]
Sent: den 2 mars 2009 21:41
To: Apache Torque Users List
Subject: Re: Multiple addJoin with OR
Ludwig Magnusson wrote:
> 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
I still don't think it makes sense using joins here:
When joining tables you usually "combine" rows of multiple tables
(interested in results from both tables). What you're trying to do is
selecting only categories (from table a) and you don't seem to be
interested in the corresponding entries in tables b and c.
It really seems a subquery is the right thing to use here:
SELECT * FROM category a WHERE EXISTS ( SELECT * FROM bought b where
a.id = b.fk ) OR EXISTS ( SELECT * FROM sold c where a.id = c.fk);
Im also not sure if your join works if you'd use the ON-Clause that is
usually ment for join-conditions instead of WHERE:
SELECT * FROM a JOIN b ON a.id = b.fk OR JOIN c ON a.id = c.id
OR is not allowed here ---^
If you execute the statement you describe the DBMS (IMHO!) actually
produces a Cartesian product and applies the WHERE-Conditions "afterwards":
SELECT Category.* from a, b, c WHERE a.id = b.fk OR a.id = c.fk
This means (no DBMS-internal optimization assumed) the DBMS first
"combines" each row of a with all rows of b and then combines all of
these rows to all rows of c. This means you'll have a "virtual table" of
rows_a * rows_b * rows_c size which get reduced by the WHERE-Clause
afterwards. No real "join" as you try to approach, i think ;)
See for example http://dev.mysql.com/doc/refman/5.0/en/join.html for
more information about JOINs
Just my 2ct.
Greetings,
Marc
-
To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org
For additional commands, e-mail: torque-user-h...@db.apache.org
-
To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org
For additional commands, e-mail: torque-user-h...@db.apache.org
Re: Multiple addJoin with OR
Ludwig Magnusson wrote: > 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 I still don't think it makes sense using joins here: When joining tables you usually "combine" rows of multiple tables (interested in results from both tables). What you're trying to do is selecting only categories (from table a) and you don't seem to be interested in the corresponding entries in tables b and c. It really seems a subquery is the right thing to use here: SELECT * FROM category a WHERE EXISTS ( SELECT * FROM bought b where a.id = b.fk ) OR EXISTS ( SELECT * FROM sold c where a.id = c.fk); Im also not sure if your join works if you'd use the ON-Clause that is usually ment for join-conditions instead of WHERE: SELECT * FROM a JOIN b ON a.id = b.fk OR JOIN c ON a.id = c.id OR is not allowed here ---^ If you execute the statement you describe the DBMS (IMHO!) actually produces a Cartesian product and applies the WHERE-Conditions "afterwards": SELECT Category.* from a, b, c WHERE a.id = b.fk OR a.id = c.fk This means (no DBMS-internal optimization assumed) the DBMS first "combines" each row of a with all rows of b and then combines all of these rows to all rows of c. This means you'll have a "virtual table" of rows_a * rows_b * rows_c size which get reduced by the WHERE-Clause afterwards. No real "join" as you try to approach, i think ;) See for example http://dev.mysql.com/doc/refman/5.0/en/join.html for more information about JOINs Just my 2ct. Greetings, Marc - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
Re: Multiple addJoin with OR
Ludwig Magnusson wrote: > 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. I still suspect that this is error prone. I'd be interested in the output of "explain". > 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. I'd suggest to right join table b to a, left join table a to c, check for not null and be sure to set distinct (you did that anyway, didn't you?) Bye, Thomas. - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
RE: Multiple addJoin with OR
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
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
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
RE: Multiple addJoin with OR
-Original Message- From: Thomas Fischer [mailto:fisc...@seitenbau.net] Sent: den 2 mars 2009 14:27 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 - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
RE: Multiple addJoin with OR
I thougt of that as well and it generates a InvocationTargetException that has a NullPointerException. I ran it through a debug but could not find the source.. -Original Message- From: Hidde Boonstra [Us Media] [mailto:hidde.boons...@usmedia.nl] Sent: den 2 mars 2009 14:18 To: Apache Torque Users List Subject: Re: Multiple addJoin with OR Hi Ludwig, never done that, but I would suppose you could use criteria.getNewCriterion and use the SqlEnum.JOIN as your comparison. Than use criteria.criterion.or to create an or between the two. See: http://db.apache.org/torque/releases/torque-3.3/runtime/apidocs/org/apache/torque/util/Criteria.html http://db.apache.org/torque/releases/torque-3.3/runtime/apidocs/org/apache/torque/util/SqlEnum.html http://db.apache.org/torque/releases/torque-3.3/runtime/apidocs/org/apache/torque/util/Criteria.Criterion.html Regards, Hidde. - "Ludwig Magnusson" wrote: > Hi! > > 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? > > /Ludwig -- Hidde Boonstra Us Media B.V. Stadhouderskade 115 1073 AX Amsterdam t: 020 428 68 68 f: 020 470 69 05 www.usmedia.nl - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
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
Re: Multiple addJoin with OR
Hi Ludwig, never done that, but I would suppose you could use criteria.getNewCriterion and use the SqlEnum.JOIN as your comparison. Than use criteria.criterion.or to create an or between the two. See: http://db.apache.org/torque/releases/torque-3.3/runtime/apidocs/org/apache/torque/util/Criteria.html http://db.apache.org/torque/releases/torque-3.3/runtime/apidocs/org/apache/torque/util/SqlEnum.html http://db.apache.org/torque/releases/torque-3.3/runtime/apidocs/org/apache/torque/util/Criteria.Criterion.html Regards, Hidde. - "Ludwig Magnusson" wrote: > Hi! > > 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? > > /Ludwig -- Hidde Boonstra Us Media B.V. Stadhouderskade 115 1073 AX Amsterdam t: 020 428 68 68 f: 020 470 69 05 www.usmedia.nl - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
Multiple addJoin with OR
Hi! 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? /Ludwig
