db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ant...@di.uoa.gr
Subject RE: Problems to set up a subquery
Date Mon, 16 Oct 2006 21:37:43 GMT
Hello,

  One thing that I have found very useful in complex queries is to bypass the
Criteria objects.
  All you have to do is to write the SQL query, execute it using
BasePeer.executeQuery() to get a List of Records and then create a list of
persistent objects using the populateObjects of the corresponding Peer class.
  In this case, you can use:

String query = "SELECT DISTINCT * FROM kundenadresse WHERE kundenadress_id 
    NOT IN (SELECT kundenadress_id FROM adressenreferenz)";
List kundenadresses = 
    KundenadressePeer.populateObjects(BasePeer.executeQuery(query));

  It works fine if you have a query of the form: "select * from foo...", ie you
select all the columns of a single table. I don't have the source code handy to
check if this query would work (my feeling is that this would work also):

  String query = "SELECT * FROM FOO f, BAR b WHERE f.b = b.f AND ...";
  List list = FooPeer.populateObjects(BasePeer.executeQuery(query));

Enjoy,
Antonis

> Hi,
> 
> Subqueries are implemented and documented in the svn version(3.2.1-dev),
> but not in Torque 3.2.0.
> Either build Torque from svn or, if you want to use Torque 3.2.0. you can
> code the subquery as custom criteria (see
> http://db.apache.org/torque/releases/torque-3.2
> /runtime/reference/read-from-db.html#Using_the_CUSTOM_modifier_to_use_custom_SQL)
>  (you can also build the subquery via another Criteria and its
> toString()-method if you do not want to hand-code it).
> 
>     Thomas
> 
> "Pfiester, Jan" <jan.pfiester@ict.fraunhofer.de> schrieb am 16.10.2006
> 17:40:05:
> 
> > Hi everyone,
> >
> > I ran into problems to setup the following query using criteria:
> >
> > SELECT DISTINCT * FROM kundenadresse
> > WHERE kundenadress_id NOT IN(
> >    SELECT kundenadress_id FROM adressenreferenz
> > );
> >
> > Could someone please give me some help to come up with a proper solution?
> > Hints for further reading on this topic would also be very much
> appreciated!
> >
> > Many thanks in advance,
> > Jan
> >
> > ---------------------------------------------------------------------
> > 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
> 
> 


-- 



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