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
AW: Problems to set up a subquery
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, >> >&g
AW: Problems to set up a subquery
I did not see this message before nor do I know what it means. I would have thought this should work But then, village comes up with surprises now and then. >From the message, village does not seem to allow just a single column. Have you tried to add another ? Thomas "Pfiester, Jan" <[EMAIL PROTECTED]> schrieb am 17.10.2006 13:44:46: > 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 wayto 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 wayto 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 cascadedone... > > 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 >
AW: Problems to set up a subquery
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); Many thx in Advance, Jan -Ursprüngliche Nachricht- Von: Thomas Vandahl [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 19. Oktober 2006 17:52 An: Apache Torque Users List Betreff: Re: Problems to set up a subquery 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. [...] > 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. I second that. It is sometimes surprising what *can* be done with the Torque Criteria. Custom SQL breaks the basic idea of Torque. Bye, Thomas. - 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]
AW: Problems to set up a subquery
> Sort of a "clean" approach, but not working due sql exections: > [...] > criteria.addNotIn(KundenadressePeer.KUNDENADRESS_ID, > AdressenreferenzPeer.doSelect(subCriteria)); > [...] Just a question because I'm not sure if you just show an example or the real code, that you're using. The doSelect() method in your addNotIn() returns a list of Adressreferenz objects. If you put this into a addNotIn - I think the query-generation-process uses the String representation of these Adressreferenz objects to create the sql-query. Did you overwrite the toString() method in Adressreferenz? I'm not sure, if you have to do this, but my Torque version (3.2) uses toString() to create the query. The generated toString() method in BaseAdressreferenz is nice for debugging but useless for sql-queries, I think. bye Thoralf - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
AW: Problems to set up a subquery
Well, it is some real code... > Did you overwrite the toString() method in Adressreferenz? No, I did'nt. > but my Torque version (3.2) uses toString() to create the query. The > > generated toString() method in BaseAdressreferenz is nice for > debugging but useless for sql-queries... So what are you doing in the overwritten toString() method? Do you just build together a subselect-querystring arrayobject according to the returned result objects? Don't you have to hard code row references again (see the custom-tag), which is what i'm trying to avoid. Bye Jan -Ursprüngliche Nachricht- Von: Thoralf Rickert [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 20. Oktober 2006 11:24 An: Apache Torque Users List Betreff: AW: Problems to set up a subquery > Sort of a "clean" approach, but not working due sql exections: > [...] > criteria.addNotIn(KundenadressePeer.KUNDENADRESS_ID, > AdressenreferenzPeer.doSelect(subCriteria)); > [...] Just a question because I'm not sure if you just show an example or the real code, that you're using. The doSelect() method in your addNotIn() returns a list of Adressreferenz objects. If you put this into a addNotIn - I think the query-generation-process uses the String representation of these Adressreferenz objects to create the sql-query. Did you overwrite the toString() method in Adressreferenz? I'm not sure, if you have to do this, but my Torque version (3.2) uses toString() to create the query. The generated toString() method in BaseAdressreferenz is nice for debugging but useless for sql-queries, I think. bye Thoralf - 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]
AW: Problems to set up a subquery
You have three options: Overwrite toString and return the KundenaddressId that is set in the reference (because that is what you want). The other solution is to make a doSelectVillageRecords (that returns only the columns that you have ask for with subCriteria.addSelectColumn()). The doSelectVillageRecords would return a list of Village Record objects. But you don't have access to the toString() method, so you have to extract the values in an extra array... The third method is to use a join. The third method seems to be the best, because everything else results in two select queries (first to get Adressreferenz Ids and then to make the IN-Query). > -Ursprüngliche Nachricht- > Von: Pfiester, Jan [mailto:[EMAIL PROTECTED] > Gesendet: Freitag, 20. Oktober 2006 12:18 > An: Apache Torque Users List > Betreff: AW: Problems to set up a subquery > > > Well, it is some real code... > > > Did you overwrite the toString() method in Adressreferenz? > No, I did'nt. > > > but my Torque version (3.2) uses toString() to create the > query. The > > > generated toString() method in BaseAdressreferenz is nice for > > debugging but useless for sql-queries... > > So what are you doing in the overwritten toString() method? > Do you just build together a subselect-querystring > arrayobject according to the returned result objects? Don't > you have to hard code row references again (see the > custom-tag), which is what i'm trying to avoid. > > Bye > Jan > > > -Ursprüngliche Nachricht- > Von: Thoralf Rickert [mailto:[EMAIL PROTECTED] > Gesendet: Freitag, 20. Oktober 2006 11:24 > An: Apache Torque Users List > Betreff: AW: Problems to set up a subquery > > > > Sort of a "clean" approach, but not working due sql exections: [...] > > criteria.addNotIn(KundenadressePeer.KUNDENADRESS_ID, > > AdressenreferenzPeer.doSelect(subCriteria)); > > [...] > > Just a question because I'm not sure if you just show an > example or the real code, that you're using. The doSelect() > method in your addNotIn() returns a list of Adressreferenz > objects. If you put this into a addNotIn - I think the > query-generation-process uses the String representation of > these Adressreferenz objects to create the sql-query. Did you > overwrite the toString() method in Adressreferenz? I'm not > sure, if you have to do this, but my Torque version (3.2) > uses toString() to create the query. The generated toString() > method in BaseAdressreferenz is nice for debugging but > useless for sql-queries, I think. > > bye > Thoralf > > > - > 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]
AW: Problems to set up a subquery
Many thx! As i've just stated, i had tried to do it by using an outer join at first too, but i did'nt know how to eliminate the intersection of both tables in the result set... -Ursprüngliche Nachricht- Von: Thoralf Rickert [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 20. Oktober 2006 12:45 An: Apache Torque Users List Betreff: AW: Problems to set up a subquery You have three options: Overwrite toString and return the KundenaddressId that is set in the reference (because that is what you want). The other solution is to make a doSelectVillageRecords (that returns only the columns that you have ask for with subCriteria.addSelectColumn()). The doSelectVillageRecords would return a list of Village Record objects. But you don't have access to the toString() method, so you have to extract the values in an extra array... The third method is to use a join. The third method seems to be the best, because everything else results in two select queries (first to get Adressreferenz Ids and then to make the IN-Query). > -Ursprüngliche Nachricht- > Von: Pfiester, Jan [mailto:[EMAIL PROTECTED] > Gesendet: Freitag, 20. Oktober 2006 12:18 > An: Apache Torque Users List > Betreff: AW: Problems to set up a subquery > > > Well, it is some real code... > > > Did you overwrite the toString() method in Adressreferenz? > No, I did'nt. > > > but my Torque version (3.2) uses toString() to create the > query. The > > > generated toString() method in BaseAdressreferenz is nice for > > debugging but useless for sql-queries... > > So what are you doing in the overwritten toString() method? > Do you just build together a subselect-querystring arrayobject > according to the returned result objects? Don't you have to hard code > row references again (see the custom-tag), which is what i'm trying to > avoid. > > Bye > Jan > > > -Ursprüngliche Nachricht- > Von: Thoralf Rickert [mailto:[EMAIL PROTECTED] > Gesendet: Freitag, 20. Oktober 2006 11:24 > An: Apache Torque Users List > Betreff: AW: Problems to set up a subquery > > > > Sort of a "clean" approach, but not working due sql exections: [...] > > criteria.addNotIn(KundenadressePeer.KUNDENADRESS_ID, > > AdressenreferenzPeer.doSelect(subCriteria)); > > [...] > > Just a question because I'm not sure if you just show an example or > the real code, that you're using. The doSelect() method in your > addNotIn() returns a list of Adressreferenz objects. If you put this > into a addNotIn - I think the query-generation-process uses the String > representation of these Adressreferenz objects to create the > sql-query. Did you overwrite the toString() method in Adressreferenz? > I'm not sure, if you have to do this, but my Torque version (3.2) uses > toString() to create the query. The generated toString() method in > BaseAdressreferenz is nice for debugging but useless for sql-queries, > I think. > > bye > Thoralf > > > - > 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]
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
AW: AW: Problems to set up a subquery
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:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: AW: AW: Problems to set up a subquery
Pfiester, Jan wrote: > 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? You can as well use criteria.add(AdressenreferenzPeer.KUNDENADRESS_ID, NULL); This is mapped internally but I consider the explicit ISNULL comparison being easier to read. That's why the dummy parameter. Bye, Thomas. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
