db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ludwig Magnusson" <lud...@itcatapult.com>
Subject RE: Multiple addJoin with OR
Date Mon, 02 Mar 2009 14:02:03 GMT

-----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


Mime
View raw message