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: Tue, 15 Feb 2022 07:52:04 -0500
OC,
I do not see your point about the qualifierWithQualifierFormat. Unless your
users are writing the format string directly, you have code to build it.
Building a qualifier format string or a qualifier object is the same thing, it
is just easier and less error prone to build the object using ERXAndQualifier
and ERXORQualifier...
WODisplayGroup can issue fetch to a database or use an array of object as
source. See setObjectArray(NSArray objects). I always use it this way.
If your data size is small, you can also filter in memory and set the resulting
array as your WODisplayGroup source.
Regards,
Samuel
> Le 15 févr. 2022 à 00:59, OCsite <email@hidden> a écrit :
>
> Thanks to all for the answers!
>
> Is there a way to use the ERExistsQualifier through
> qualifierWithQualifierFormat? At the particular place it would be somewhat
> difficult to create the qualifier tree programmatically, there are
> considerably more complex qualifiers ANDed and ORred to the thing, which I
> did not show for clarity, for they do not affect the problem.
>
> (Also, it would be essentially impossible to do two fetches — the final
> qualifier is used through a WODisplayGroup).
>
> Thanks a lot again,
> OC
>
>> On 15. 2. 2022, at 0:29, Samuel Pelletier <email@hidden
>> <mailto:email@hidden>> wrote:
>>
>> 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 <mailto: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 <mailto:email@hidden>
>>>
>>> _______________________________________________
>>> 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 <mailto: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