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 Tue, 17 Oct 2006 13:20:27 GMT
I agree with you, but there are a few cases that there is no alternative:
    delete all records from a table ( FooPeer.doDelete(new Criteria()) 
will not work). We had to iterate over the list of all records and call 
delete for each one.
    complex queries with subqueries and/or multiple references to the 
same column. It can be done using Criteria, but some times I find it 
much easier to create the query by hand, especially if many parts of the 
query are optional.

Antonis

Thomas Fischer wrote:

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

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