db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Vandahl ...@apache.org>
Subject Re: AW: Problems to set up a subquery
Date Fri, 20 Oct 2006 10:24:11 GMT
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


Mime
View raw message