On 2012-05-19, at 8:55 AM, Mark Gowdy wrote: > Thanks Chuck, > Half the battle with this stuff is knowing what road to choose.
There are many roads and no maps. :-)
> When I constructed my query in the following way, it worked correctly.
>
> EOQualifier q1 =
> Media.PRIMARY_CATEGORY.eq(cat).or(Media.SECONDARY_CATEGORY.eq(cat)); //
> result count = 16 (correct)
> EOQualifier q2 = Media.OTHER_CATEGORIES.containsObject(cat); // result count
> = 11 (correct)
> // Turn q2 into a subquery
> ERXQualifierInSubquery q2AsSubQuery = new ERXQualifierInSubquery(q2);
> ERXOrQualifier finalQuery = new ERXOrQualifier(new NSArray( new EOQualifier[]
> {q1, q2AsSubQuery } )); // result count = 27 (CORRECT !!)
>
> The SQL it generates is:
> SELECT DISTINCT t0.MEDIA_PK, t0.PRIMARY_CAT_FK, t0.SECONDARY_CAT_FK
> FROM mb_media t0
> WHERE (t0.MEDIA_PK IN ( SELECT t0.MEDIA_PK FROM mb_media t0,
> mb_cat_media_join T1, mb_categories T2 WHERE T2.CAT_PK = ? AND t0.MEDIA_PK =
> T1.MEDIA_CK AND T1.CAT_CK = T2.CAT_PK ) OR (t0.SECONDARY_CAT_FK = ? OR
> t0.PRIMARY_CAT_FK = ?))
>
>
> The only problem now is, that
> fetchSpec.setPrefetchingRelationshipKeyPaths(new
> NSArray<String>(Media.TO_ONE_INFO)); no longer works.
> CLASS : java.lang.IllegalStateException
> MESSAGE : sqlStringForKeyValueQualifier: attempt to generate SQL for
> er.extensions.qualifiers.ERXKeyValueQualifier (otherCategories contains
> (com.aetopia.MediaCategory)'<com.aetopia.MediaCategory pk:"1118">') failed
> because attribute identified by key 'otherCategories' was not reachable from
> from entity 'MediaInfo'
>
> But I think I can probably live with that for the minute.
That is probably a bug in ERXQualifierInSubquery. Good luck finding and fixing
it! :-) Sounds like one of the qualifiers needs to be migrated. See
EOQualifierSQLGeneration.
There are also the Houdah qualifiers. One of them may work and avoid this
prefetching bug. I am pretty sure that is what I did when I hit this. Easier
to switch than fight. :-)
>
>
> Interestingly, the javadoc of ERXQualifierInSubquery mentions:
> "This class can be used to work around the EOF bug where OR queries involving
> many-to-manies are incorrectly generated"
>
>
> ERXQualifierInSubquery
> ----------------
>
> Generates a subquery for the qualifier given in argument:
>
>
> EOQualifier q = EOQualifier.qualifierWithQualifierFormat("firstName =
> 'Max'", null);
> ERXQualifierInSubquery qq = new ERXQualifierInSubquery(q, "User", "group");
> EOFetchSpecification fs = new EOFetchSpecification("Group", qq, null);
>
>
> Would generate: "SELECT t0.GROUP_ID, t0.NAME FROM USER t0 WHERE t0.GROUP_ID
> IN ( SELECT t0.GROUP_ID FROM GROUP t0 WHERE t0.NAME = ? ) " This class can be
> used to work around the EOF bug where OR queries involving many-to-manies are
> incorrectly generated
> It will also generate ... t0.FOREIGN_KEY_ID in (select t1.ID from X where
> [your qualifier here]) with the 3 arg constructor
> -------------------
>
> My head just melted.
Qualifier code can do that.
Chuck
>
> On 18 May 2012, at 22:09, Mark Gowdy wrote:
>
>> Nope, MySQL
>>
>> I believe the equivalent memory filter works correctly.
>>
>> Mark
>>
>> Sent from my iPhone
>>
>> On 18 May 2012, at 19:51, Ramsey Gurley <[email protected]> wrote:
>>
>>> Using OpenBase by chance? I remember having issues with OR qualifiers on
>>> that a couple years ago. The qualifiers would only work correctly in
>>> memory. Using them on a db fetch failed to return the correct results. I
>>> never delved into the sql at the time, I just went with in memory
>>> qualifying since it worked.
>>>
>>> Ramsey
>>>
>>> On May 18, 2012, at 5:20 AM, Mark Gowdy wrote:
>>>
>>>>
>>>> On 17 May 2012, at 18:27, Chuck Hill wrote:
>>>>
>>>>> What are the three SQL statements being generated. That is usually where
>>>>> to start looking.
>>>>
>>>> For some reason, I cannot get EOAdaptorDebugEnabled to work. So I turned
>>>> on sql logging (MySQL) using:
>>>> SET GLOBAL general_log = 'ON';
>>>>
>>>> Some context:
>>>> We have Media and MediaCategory entities with the following relationships:
>>>> Media.primaryCat << -- --> MediaCategory
>>>> Media.secondaryCat << -- --> MediaCategory
>>>> Media.otherCats << -- 'mb_cat_media_join' -->> MediaCategory
>>>>
>>>> These are the 4 queries and the resulting SQL:
>>>>
>>>> EOQualifier q1 =
>>>> Media.PRIMARY_CATEGORY.eq(cat).or(Media.SECONDARY_CATEGORY.eq(cat));
>>>> // result count = 16 (CORRECT)
>>>> SELECT DISTINCT t0.MEDIA_PK, t0.PRIMARY_CAT_FK, t0.SECONDARY_CAT_FK
>>>> FROM mb_media t0
>>>> WHERE (t0.SECONDARY_CAT_FK = ? OR t0.PRIMARY_CAT_FK = ?)
>>>>
>>>> EOQualifier q2 = Media.OTHER_CATEGORIES.containsObject(cat);
>>>> // result count = 11 (CORRECT)
>>>> SELECT DISTINCT t0.MEDIA_PK, t0.PRIMARY_CAT_FK, t0.SECONDARY_CAT_FK
>>>> FROM mb_media t0, mb_cat_media_join T1, mb_categories T2
>>>> WHERE T2.CAT_PK = ? AND t0.MEDIA_PK = T1.MEDIA_CK AND T1.CAT_CK = T2.CAT_PK
>>>>
>>>> Both of the above are correct, but when you try to OR them (in q3 and q4
>>>> below):
>>>>
>>>> EOQualifier q3 =
>>>> Media.PRIMARY_CATEGORY.eq(cat).or(Media.SECONDARY_CATEGORY.eq(cat)).or(Media.OTHER_CATEGORIES.containsObject(cat));
>>>> // result count = 15 (WRONG, it should be 27)
>>>> SELECT DISTINCT t0.MEDIA_PK, t0.PRIMARY_CAT_FK, t0.SECONDARY_CAT_FK
>>>> FROM mb_media t0, mb_cat_media_join T1, mb_categories T2
>>>> WHERE ((T2.CAT_PK = ? OR t0.SECONDARY_CAT_FK = ?) OR t0.PRIMARY_CAT_FK =
>>>> ?) AND t0.MEDIA_PK = T1.MEDIA_CK AND T1.CAT_CK = T2.CAT_PK
>>>>
>>>> EOOrQualifier q4 = new EOOrQualifier(new NSArray( new EOQualifier[] {q1,q2
>>>> } ));
>>>> // result count = 15 (WRONG, it should be 27)
>>>> SELECT DISTINCT t0.MEDIA_PK, t0.PRIMARY_CAT_FK, t0.SECONDARY_CAT_FK
>>>> FROM mb_media t0, mb_cat_media_join T1, mb_categories T2
>>>> WHERE (T2.CAT_PK = ? OR (t0.SECONDARY_CAT_FK = ? OR t0.PRIMARY_CAT_FK =
>>>> ?)) AND t0.MEDIA_PK = T1.MEDIA_CK AND T1.CAT_CK = T2.CAT_PK
>>>>
>>>>
>>>>
>>>> If I run this _manually_ generated SQL, it give a result of 27 (which is
>>>> correct):
>>>> SELECT DISTINCT t0.MEDIA_PK, t0.PRIMARY_CAT_FK, t0.SECONDARY_CAT_FK
>>>> FROM mb_media t0, mb_cat_media_join T1, mb_categories T2
>>>> WHERE (t0.SECONDARY_CAT_FK = 2 OR t0.PRIMARY_CAT_FK = 2) OR (T2.CAT_PK = 2
>>>> AND t0.MEDIA_PK = T1.MEDIA_CK AND T1.CAT_CK = T2.CAT_PK)
>>>>
>>>> Any ideas how I can get WO to do the right thing?
>>>> (it looks like an EO sql generation bug to me)
>>>>
>>>> Many thanks,
>>>>
>>>> Mark
>>>>
>>>>
>>>>>
>>>>> Chuck
>>>>>
>>>>>
>>>>> On 2012-05-17, at 10:17 AM, Mark Gowdy wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I have a 'Media' entity, and a 'Category' entity.
>>>>>> I need to create a qualifier that queries on 2 'to-one' relationships,
>>>>>> as well as on a single 'to-many'.
>>>>>>
>>>>>>
>>>>>> These are some test qualifiers and the number of distinct results they
>>>>>> got:
>>>>>>
>>>>>> EOQualifier q1 =
>>>>>> Media.PRIMARY_CATEGORY.eq(cat).or(Media.SECONDARY_CATEGORY.eq(cat)); //
>>>>>> result count = 16 (correct)
>>>>>> //
>>>>>> EOQualifier q2 = Media.OTHER_CATEGORIES.containsObject(cat); // result
>>>>>> count = 11 (correct)
>>>>>> //
>>>>>> EOQualifier q3 =
>>>>>> Media.PRIMARY_CATEGORY.eq(cat).or(Media.SECONDARY_CATEGORY.eq(cat)).or(Media.OTHER_CATEGORIES.containsObject(cat));
>>>>>> // result count = 15 (WRONG, should be 27)
>>>>>> //
>>>>>> // Another way
>>>>>> EOOrQualifier q4 = new EOOrQualifier(new NSArray( new EOQualifier[]
>>>>>> {q1,q2 } )); // result count = 15 (WRONG)
>>>>>>
>>>>>>
>>>>>> In the past, I worked around the problem by doing two separate fetches
>>>>>> and combining the results. But I would like to do it in one.
>>>>>> So, any ideas how can I get 'q3' to work?
>>>>>>
>>>>>> The main reason for fixing this, is that I need to combine a few of
>>>>>> these with a larger AND qualifier, and some of the intermediary
>>>>>> qualifiers are likely to have LOTS of results.
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> Mark
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> Do not post admin requests to the list. They will be ignored.
>>>>>> Webobjects-dev mailing list ([email protected])
>>>>>> Help/Unsubscribe/Update your Subscription:
>>>>>> https://lists.apple.com/mailman/options/webobjects-dev/chill%40global-village.net
>>>>>>
>>>>>> This email sent to [email protected]
>>>>>
>>>>> --
>>>>> Chuck Hill Senior Consultant / VP Development
>>>>>
>>>>> Practical WebObjects - for developers who want to increase their overall
>>>>> knowledge of WebObjects or who are trying to solve specific problems.
>>>>> http://www.global-village.net/gvc/practical_webobjects
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>> _______________________________________________
>>>> Do not post admin requests to the list. They will be ignored.
>>>> Webobjects-dev mailing list ([email protected])
>>>> Help/Unsubscribe/Update your Subscription:
>>>> https://lists.apple.com/mailman/options/webobjects-dev/rgurley%40smarthealth.com
>>>>
>>>> This email sent to [email protected]
>>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list ([email protected])
>> Help/Unsubscribe/Update your Subscription:
>> https://lists.apple.com/mailman/options/webobjects-dev/gowdy%40mac.com
>>
>> This email sent to [email protected]
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list ([email protected])
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/chill%40global-village.net
>
> This email sent to [email protected]
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/gvc/practical_webobjects
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to [email protected]
