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 <sam...@samkar.com> 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 >> <webobjects-dev@lists.apple.com <mailto:webobjects-dev@lists.apple.com>> 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 >>> <webobjects-dev@lists.apple.com <mailto:webobjects-dev@lists.apple.com>> >>> 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 (Webobjects-dev@lists.apple.com >>> <mailto:Webobjects-dev@lists.apple.com>) >>> Help/Unsubscribe/Update your Subscription: >>> https://lists.apple.com/mailman/options/webobjects-dev/aaron%40chatnbike.com >>> >>> <https://lists.apple.com/mailman/options/webobjects-dev/aaron%40chatnbike.com> >>> >>> This email sent to aa...@chatnbike.com <mailto:aa...@chatnbike.com> >> >> _______________________________________________ >> Do not post admin requests to the list. They will be ignored. >> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com >> <mailto:Webobjects-dev@lists.apple.com>) >> Help/Unsubscribe/Update your Subscription: >> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com >> <https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com> >> >> This email sent to sam...@samkar.com >
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
