Laszlo Spoor <lspoor_cayenn..otmail.com> wrote:
> Cayenne 1.1
> MySql 4.1.8
>
> Hi Everybody,
>
> I want to find the entries that do not occur in intersection tables, so in
> SQL:
>
> select *
> from pva_fotos
> where id not in (select fto_id from pva_wng_fto)
> and id not in (select fto_id from pva_cpx_fto)
Unfortunately, I'm very weak on using the complex Expressions, but couldn't
you just translate this literally?
Expression a1 = "select fto_id from pva_wng_fto" // convert this like below
Expression a2 = ExpressionFactory.notInExp(a1, id);
Expression b1 = "select fto_id from pva_cpx_fto" // convert this like below
Expression b2 = ExpressionFactory.notInExp(b1, id);
Expression c = a2.andExp(b2);
I'm fairly certain that, if nothing else, you can switch over to using
SQLTemplates to do this.
http://objectstyle.org/cayenne/userguide/fetch/sqltemplate.html
> The JavaCode that came close to this was:
>
> [...]
> SelectQuery qry = new SelectQuery (Foto.class);
>
> SelectQuery qryCfi = new SelectQuery (Foto.class);
> Expression qc1 = Expression.fromString("toCpxFtoRelaties");
> qryCfi.setQualifier(qc1);
> List lstCfi = mContext.performQuery(qryCfi);
>
> SelectQuery qryWfi = new SelectQuery (Foto.class);
> Expression qw1 = Expression.fromString("toWngFtoRelaties");
> qryWfi.setQualifier(qw1);
> List lstWfi = mContext.performQuery(qryWfi);
>
> Expression qCpxFto = null;
> if (lstCfi.size() > 0)
> {
> qCpxFto = ExpressionFactory.notInExp("<what to say here?>", lstCfi);
> qry.setQualifier(qCpxFto);
> } //if
>
> if (lstWfi.size() > 0)
> {
> Expression qWngFto = ExpressionFactory.notInExp("<what to say here?>",
> lstWfi);
> if (qCpxFto != null)
> {
> qry.andQualifier(qWngFto);
> } //if
> else
> {
> qry.setQualifier(qWngFto);
> } //else
>
> } //if
>
> List lst = mContext.performQuery(qry);
> [..]
>
>
> Can someone answer the following questions for me? :
> - What should I insert at the "<what to say here?>" part? I want to
compare
> that object to the current List.
> - Is there a more efficient way to handle subselects? If my approach is
> correct, large resultsets of the subselects could become problematic.
>
> Thanks in advance, Laszlo
>
> _________________________________________________________________
> FREE pop-up blocking with the new MSN Toolbar - get it now!
> http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
>
This archive was generated by hypermail 2.0.0 : Wed Feb 16 2005 - 12:26:14 EST