db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <tfisc...@apache.org>
Subject RE: Problems to set up a subquery
Date Tue, 17 Oct 2006 07:35:01 GMT
Hm, writing SQL is one of the the things that Torque wants to avoid. One 
of the reasons for this is portability between databases. Though Torque is 
certainly not perfectly portable, it is much better than plain SQL. 
Another reason is that the compiler checks if the columns you use in your 
query is still in your database model (it will not find the relevant 
constants if a column is removed or renamed and the model is regenerated)
(of cousre, the constants can also be used to build SQL to have the same 
effect, but this is quite a hassle).

So in my eyes, one should think twice before using custom SQL. There are 
cases where it cannot be avoided (see below), but I would think twice 
before using it.

     Thomas

On Tue, 17 Oct 2006, antleb@di.uoa.gr wrote:

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

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