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: AW: Problems to set up a subquery
Date Fri, 20 Oct 2006 10:47:45 GMT
Thx for that one,
I've try your latest hint too, but i didn't know that you can check 
zero references like you did!

> criteria.add(AdressenreferenzPeer.KUNDENADRESS_ID, ??0??,
>	Criteria.ISNULL);

In your criteria what is the second param  in the signature doing?
What is the 0 indicating?

Oh btw, your code is working fine!
Thx again...

Jan
 

-----Ursprüngliche Nachricht-----
Von: Thomas Vandahl [mailto:tv@apache.org] 
Gesendet: Freitag, 20. Oktober 2006 12:24
An: Apache Torque Users List
Betreff: Re: AW: Problems to set up a subquery

Pfiester, Jan wrote:
> Hello Thomas,
> I totally agree with you, but still i'm not able to come up with a solution i would call
a good one.
> Can you please tell me what i'm doing wrong in the code below:
> 
> My current working solution but not considered as a good one:
> 	Criteria criteria = new Criteria();
> 	criteria.add(	
> 		KundenadressePeer.KUNDENADRESS_ID, 
> 		(Object)"kundenadresse.KUNDENADRESS_ID NOT IN (SELECT adressenreferenz.KUNDENADRESS_ID
					FROM adressenreferenz)", 
> 		Criteria.CUSTOM
> 	);
> 		
> Sort of a "clean" approach, but not working due sql exections:
> 	Criteria subCriteria = new Criteria();
> 	subCriteria.add(AdressenreferenzPeer.KUNDENADRESS_ID, Criteria.ALL);
> 		
> 	Criteria criteria = new Criteria();
> 	criteria.add(KundenadressePeer.KUNDEN_ID, kundeId, Criteria.EQUAL);
> 	criteria.addNotIn(KundenadressePeer.KUNDENADRESS_ID, 
> AdressenreferenzPeer.doSelect(subCriteria));
> 
> 	return KundenadressePeer.doSelect(criteria);
> 

My suggestion uses a join instead of a subquery:

    Criteria criteria = new Criteria();
    criteria.addJoin(KundenadressePeer.KUNDENADRESS_ID,
	AdressenreferenzPeer.KUNDENADRESS_ID,
	Criteria.LEFT_JOIN);

    criteria.add(AdressenreferenzPeer.KUNDENADRESS_ID, 0,
	Criteria.ISNULL);

    return KundenadressePeer.doSelect(criteria);


Bye, Thomas.

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