db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Pfiester, Jan" <jan.pfies...@ict.fraunhofer.de>
Subject AW: Problems to set up a subquery
Date Tue, 17 Oct 2006 11:44:46 GMT
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:jan.pfiester@ict.fraunhofer.de] 
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:tfischer@apache.org]
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, 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

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