Thanks Chuck,
Half the battle with this stuff is knowing what road to choose.
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.
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.
Mark
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/archive%40mail-archive.com
This email sent to [email protected]
