| When building a compound qualifier for a many-to-many relationship, against flattened relationships, EO doesn't seem to generate the appropriate SQL.
Structure:
Record <<-->> Group (where as these are flattened relationships with a intermediary RecordGroup join table)
Qualifier:
Records belonging to Group A --and-- NOT (Records belonging to Group B)
I've attempted to use both EOQualifier.QualifierOperatorContains and EOQualifier.QualifierOperatorEquals, both generate SQL in the form:
(not (group.groupID = ?) AND group.groupID = ?)
Through hand coding the SQL, using the join table along with IN and NOT IN operators, I've written the following SQL to return accurate results:
(recordGroup.recordID NOT IN (SELECT recordID FROM RecordGroup WHERE groupID = ?)) AND (recordGroup.recordID IN (SELECT recordID FROM RecordGroup WHERE groupID = ?))
I think I'm probably missing a simple piece of the puzzle here. All other non-compound qualifiers work fine. Any thoughts on how to build the correct qualifier?
Thanks, George
|