You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Ludwig Magnusson <lu...@itcatapult.com> on 2009/03/02 14:03:04 UTC
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
Re: Multiple addJoin with OR
Posted by Thomas Vandahl <tv...@apache.org>.
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-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Multiple addJoin with OR
Posted by Greg Monroe <mo...@dukece.com>.
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:ludwig@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:fischer@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-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@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-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Multiple addJoin with OR
Posted by Ludwig Magnusson <lu...@itcatapult.com>.
-----Original Message-----
From: Thomas Fischer [mailto:fischer@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-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Multiple addJoin with OR
Posted by Thomas Fischer <fi...@seitenbau.net>.
> > 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
Posted by Ludwig Magnusson <lu...@itcatapult.com>.
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:macmac@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-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
Re: Multiple addJoin with OR
Posted by Marc Kannegießer <ma...@gmx.org>.
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-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Multiple addJoin with OR
Posted by Ludwig Magnusson <lu...@itcatapult.com>.
-----Original Message-----
From: Thomas Fischer [mailto:fischer@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-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Multiple addJoin with OR
Posted by Thomas Fischer <fi...@seitenbau.net>.
> 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
Posted by Ludwig Magnusson <lu...@itcatapult.com>.
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.boonstra@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" <lu...@itcatapult.com> 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-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
Re: Multiple addJoin with OR
Posted by "Hidde Boonstra [Us Media]" <hi...@usmedia.nl>.
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" <lu...@itcatapult.com> 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-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org