db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <fisc...@seitenbau.net>
Subject RE: Multiple addJoin with OR
Date Mon, 02 Mar 2009 13:53:46 GMT
> > 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
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message