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 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] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
