On 2012-05-18, 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,
Take at look at the qualifiers in ERXExtensions (and in Houdah frameworks if
you don't find what you need in Wonder). There are other qualifiers you can
use to avoid using contains() like this. One of them should produce the
correct SQL. Sometimes it can take a little experimenting to get the effect
that you need.
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
>>
>>
>>
>>
>>
>>
>>
>>
>
--
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]
