Re: Wrong SQL when joining in an OR-part of the qualifier
Re: Wrong SQL when joining in an OR-part of the qualifier
- Subject: Re: Wrong SQL when joining in an OR-part of the qualifier
- From: Samuel Pelletier via Webobjects-dev <email@hidden>
- Date: Mon, 14 Feb 2022 18:29:42 -0500
Hi,
As Aaron wrote, ERExisitsQualifier will generate an SQL for your needs. You may
also change the JOIN type to change it's behaviour in SQL. EOF apply table
joins before qualifier, I suspect other ORM does the same too.
For that kind of quarry, you will probably have better performance doing 2
fetches and merge the results in memory. OR is an optimisation (index) killer
for SQL queries unless the interpreter is able to rewrite then as multiple
select using index with UNION
If your columns are not indexed, forget this comment, you are doing table scan
anyway,
Regards,
Samuel
> Le 14 févr. 2022 à 10:05, Aaron Rosenzweig via Webobjects-dev
> <email@hidden> a écrit :
>
> Hi OC,
>
> That’s called the object to relational impedance mismatch. The qualifier
> works in memory but fails when you try to invoke with SQL.
>
> To get around it, you can use an ERXExistsQualifier for your to-one
> relationship. Typically you use this with to-many relationships but it
> actually can work with to-one relationships too. The reason it works is
> because once inside the “exists” clause of SQL, it acts a bit like a
> namespace shield… in other words it’s not doing an inner-join so not negating
> things that you want to OR.
>
> Another possibility is to do multiple fetches. One with the attribute only.
> Then a second with the relationship only. Then merge the results.
>
> A third possibility is to not use an inner-join when mapping your PLIST in
> Entity Modeler, use one of the other join types… but that’s maybe not the
> best option unless you really know what you are doing.
>
> Personally, I’d give the Exists qualifier a try.
>
> Cheers,
> — Aaron
>
>
>> On Feb 14, 2022, at 1:13 AM, ocs--- via Webobjects-dev
>> <email@hidden <mailto:email@hidden>>
>> wrote:
>>
>> Hi there,
>>
>> lately, I am encountering wrong fetches (with FrontBase, if important). I
>> log out my qualifier and SQL, and the gist of the problem is a join in the
>> OR-part of the qualifier. I want to fetch rows where there's an empty
>> attribute, OR when there's a specific join:
>>
>> 06:52:06.510 DEBUG -> ((savedPresentationEndDate = null) or
>> (lastValidPriceOfferCache.user = (model.DBUser)'[rc/Registration
>> centre#1000004]'))
>>
>> Alas, SQL-level, it boils down to something entirely different:
>>
>> 06:52:06.535 DEBUG "DBAuction"@318794136 expression took 1 ms: SELECT ...
>> FROM "T_AUCTION" t0, "T_PRICE_OFFER" T1 WHERE (T1."C_CREATOR_ID" = 1000004
>> OR t0."C_PRESENTATION_END_DATE" is NULL) AND
>> t0."C_LAST_VALID_PRICE_OFFER_CACHE" = T1."C_UID"
>>
>> The SQL generator properly sets up the OR checking the right target PK, but
>> then, instead of placing the join into the OR-part where it belongs to, it
>> forces the join absolute to the entire condition — even to the NULL-check
>> which should be completely independent.
>>
>> That self-evidently is not what I need here: if t0."C_PRESENTATION_END_DATE"
>> is NULL, I want to fetch the row regardless of whatever vaue there is or is
>> not in the C_LAST_VALID_PRICE_OFFER_CACHE foreign key. Actually, if there
>> happens to be NULL in C_PRESENTATION_END_DATE, I would prefer if the
>> SQL-level would not try to join at all, for it is self-evidently
>> superfluous. I've tried manually
>>
>> SELECT * FROM "T_AUCTION" t0, "T_PRICE_OFFER" T1 WHERE
>> (t0."C_PRESENTATION_END_DATE" is NULL) OR (T1."C_CREATOR_ID" = 1000004 AND
>> t0."C_LAST_VALID_PRICE_OFFER_CACHE" = T1."C_UID")
>>
>> which is what, I believe, the SQL generator should have created from the
>> qualifier, and it seems to work (at least, it produces no error; I cannot
>> easily check whether the rows returned are OK. but they seem to t the first
>> look).
>>
>> Can perhaps anybody see how to fix the problem? (But for by generating
>> directly my SQL at the application level, which I can do if need be, but
>> would rather not, unless really the only way.)
>>
>> Thanks,
>> OC
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list (email@hidden
>> <mailto: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
_______________________________________________
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