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/archive%40mail-archive.com
This email sent to [email protected]
