db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Monroe <mon...@dukece.com>
Subject RE: Multiple addJoin with OR
Date Mon, 02 Mar 2009 17:43:56 GMT
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


Mime
View raw message