OMG, i must have been sleeping...
Problem solved...
Many thanks again to both of you!
This problem still remains unsolved:
1st: In general: The doSelect of subquery throws an exception i don't really
understand. Isn't it the right way to specify the columns you want in your
resultset?
com.workingdogs.village.DataSetException: Only 1 columns exist!
=>
subCriteria.addSelectColumn(AdressenreferenzPeer.KUNDENADRESS_ID);
similar to: SELECT kundenadress_id FROM adressenreferenz ??
May be you guys could give a hint anyway
Jan
-----Ursprüngliche Nachricht-----
Von: Pfiester, Jan [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 17. Oktober 2006 13:20
An: Apache Torque Users List
Betreff: AW: Problems to set up a subquery
Hi again,
at first many thanks to both of you for your quick response!
I hope that Antonis is not feeling offended but i agree with Thomas that the
problem should be solved by means of the framework and not via SQL-statements
written by hand.
Unfortunately, although spending the entire morning on this one, i couldn't
come up with a solution yet.
Thomas, you mentioned that using custom critera would be a good idea. This is
what i was thinking too, but I don't rely get how the custom subquery and the
parent query can be combined?
So what i did is that i followed the subquery-way like this:
Criteria criteria = new Criteria();
Criteria subCriteria = new Criteria();
subCriteria.addSelectColumn(AdressenreferenzPeer.KUNDENADRESS_ID);
System.out.println(subCriteria.toString());
results = AdressenreferenzPeer.doSelect(subCriteria);
criteria.addNotIn(AdressenreferenzPeer.KUNDENADRESS_ID,
results);
criteria.add(KundenadressePeer.KUNDEN_ID, kundeId,
Criteria.EQUAL);
criteria.setDistinct();
System.out.println(criteria.toString());
results = KundenadressePeer.doSelect(criteria);
return results;
1st: In general: The doSelect of subquery throws an exception i don't really
understand. Isn't it the right way to specify the columns you want in your
resultset?
com.workingdogs.village.DataSetException: Only 1 columns exist!
=>
subCriteria.addSelectColumn(AdressenreferenzPeer.KUNDENADRESS_ID);
similar to: SELECT kundenadress_id FROM adressenreferenz ??
2nd: I don't think that this solution does not do the job cause its actually
two queries instead of on cascaded one...
3rd: Thomas could you please give an example how you'd combine query and
subquery according to this example?
I really appreciate your help!
Many thanks in advance,
Jan
-----Ursprüngliche Nachricht-----
Von: Thomas Fischer [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 17. Oktober 2006 09:35
An: Apache Torque Users List
Cc: Thomas Fischer
Betreff: RE: Problems to set up a subquery
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, [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
>>> For additional commands, e-mail: [EMAIL PROTECTED]
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>
>
> --
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
