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: Aaron Rosenzweig via Webobjects-dev <email@hidden>
- Date: Mon, 14 Feb 2022 10:05:01 -0500
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> 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)
> 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