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(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)'') 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 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
