Re: ERXExistsQualifier
Re: ERXExistsQualifier
- Subject: Re: ERXExistsQualifier
- From: Fabian Peters <email@hidden>
- Date: Thu, 23 Apr 2015 22:06:16 +0200
Hi Thomas,
AFAIK, it should work for multiple tables, except if any of them use compound primary keys. You may want to try the ExistsInRelationshipQualifier from the Houdah frameworks, which does handle flattened many-to-many relations. <https://code.google.com/p/houdah-webobjects-frameworks/> To use the qualifier, you'll need to include HoudahEOControl and HoudahEOAccess. I have disabled the custom Houdah DatabaseDelegate in com.houdah.eoaccess.PrincipalClass, as it caused strange behaviour during PK generation.
Fabian
> Am 23.04.2015 um 17:26 schrieb Thomas LATTER <email@hidden>:
>
> Hi,
>
> The ERXExistsQualifier currently doesn't seem to work when multiple tables are referenced in the 'exists' condition. I think a few people are already aware of this but could anyone tell be if they know a way to work around this ? Because the only alternatives I see are either redesigning tables in my database or trying to correct the ERXExistsQualifier.class myself. Thanks in advance for any advice!
>
>
> Here's the SQL output of two examples explaining why one works and not the other:
>
> 1) Example 1 - where the exists condition only refers to one table
> (works because OBS table is correctly aliased exists0)
>
> SELECT t0.*
> FROM PTF t0
> WHERE ( EXISTS ( SELECT exists0.ID
> FROM OBS exists0
> WHERE exists0.OBS_DATE BETWEEN date1 AND date2
> AND exists0.PTF_ID = t0.ID
> )
> )
>
> 2) Example 2 - where the exists condition refers to multiple tables
> (doesn't work because OBS table is aliased t0 instead of exists0, ...and the PTF_LOC table shouldn't be aliased T1 either)
> SELECT t0.*
> FROM PTF t0
> WHERE ( EXISTS ( SELECT exists0.ID
> FROM OBS t0, PTF_LOC T1
> WHERE T1.LAT BETWEEN -40 AND 91
> AND exists0.PTF_LOC_ID = T1.ID
> AND exists0.PTF_ID = t0.ID
> )
> )
> Next exception:SQL State:42000 -- error code: 904 -- msg: ORA-00904: "EXISTS0"."PTF_ID" : identificateur non valide
>
>
>
>
> ...And here's the 2 equivalent queries but with the IN qualifier(same problem):
>
> 1bis) SELECT t0.*
> FROM PTF t0, PROGRAM T1, MASTER_PROG T2, PTF_STATUS T3
> WHERE (t0.ID IN ( SELECT exists0.PTF_ID
> FROM OBS exists0
> WHERE exists0.OBS_DATE BETWEEN ? AND ? )
> )
>
> 2bis) SELECT t0.*
> FROM PTF t0
> WHERE (t0.ID IN ( SELECT exists0.PTF_ID
> FROM OBS t0, PTF_LOC T1
> WHERE T1.LAT BETWEEN ? AND ?
> AND exists0.PTF_LOC_ID = T1.ID
> )
> )
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden