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