db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <fisc...@seitenbau.net>
Subject AW: Problems to set up a subquery
Date Thu, 19 Oct 2006 08:22:53 GMT
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" <jan.pfiester@ict.fraunhofer.de> 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: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
>


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