Re: many-to-many query question
Re: many-to-many query question
- Subject: Re: many-to-many query question
- From: Chuck Hill <email@hidden>
- Date: Fri, 26 Oct 2007 10:10:48 -0700
On Oct 25, 2007, at 8:59 PM, Lachlan Deck wrote:
On 26/10/2007, at 12:47 PM, Chuck Hill wrote:
On Oct 25, 2007, at 6:29 PM, Lachlan Deck wrote:
On 26/10/2007, at 10:40 AM, James Cicenia wrote:
I have a many-to-many relationship as such:
Portfolio --->>MetricGroups--->>MetricItems<----project_item----
>Project
How I can create a query where
project can have ( Items(a) OR Items(b) ) AND (Items(c))
Something like this...
NSMutableArray itemsAQuals = new NSMutableArray();
String keyPathToA = "blah.blah";
for ( Item a : itemsA ) {
itemsAQuals.addObject( new EOKeyValueQualifier( keyPathToA,
EOQualifier.QualifierOperatorContains, a ) );
}
etc...
EOQualifier q = new EOAndQualifier( new NSArray( new Object[] {
new EOOrQualifier(new NSArray(new Object[] {
new EOAndQualifier( itemsAQuals ),
new EOAndQualifier( itemsBQuals )
} ) ),
new EOAndQualifier( itemsCQuals )
} ) );
That will work in memory, but
EOQualifier.QualifierOperatorContains won't generate correct SQL
for most databases.
News to me. Which databases do you think this affects? MySql is
being used here...
That is a good question. I have not re-tested this for a while so I
did so. Here is the code that I used to test this:
ec.lock();
Zone zone = Zone.zoneWithID(ec, new Long(100));
EOQualifier qual = new EOKeyValueQualifier( "zones",
EOQualifier.QualifierOperatorContains, zone );
EOFetchSpecification fetchSpec = new EOFetchSpecification
(OrderDistribution.ORDERDISTRIBUTION_ENTITY_NAME,
qual, null);
NSLog.out.appendln(ec.objectsWithFetchSpecification(fetchSpec));
ec.unlock();
FrontBase produces:
SELECT etc. etc.
FROM "OrderDistribution" t0, "OrderDistributionZoneX" T1, "PPZone" T2
WHERE T2."oid" like 100 AND t0."oid" = T1."oidOrderDistribution" AND
T1."oidZone" = T2."oid"
Which fails with
Next exception:SQL State:00 subclass = " -- error code: 208 --
msg: Semantic error 208. Expression in LIKE is not of type CHARACTER.*
Then I tried MS SQL (the only other db that I have ready to go)
produces:
SELECT etc. etc.
FROM OrderDistribution t0, OrderDistributionZoneX T1, PPZone T2
WHERE T2.oid = ? AND t0.oid = T1.oidOrderDistribution AND T1.oidZone
= T2.oid" withBindings: 1:100(oid)>
Which is correct. So maybe this is mostly a bug with FrontBase. It
used to work with FrontBase, once, long ago. I recall trying this on
other database (some or all of Oracle, DB2, OpenBase IIRC) and it did
not consistently work. For that reason, I stopped using it. It is
something to be aware of if you plan to target other databases.
Chuck
Do you use one that this works on?
I've seen problems when not using 'contains' when crossing toMany
boundaries rather than the opposite. I could paste various usages
of QualifierOperatorContains <here> but are you suggesting that it
your experience it doesn't work at all for db fetches or just
across more complex relational boundaries as above?
with regards,
--
Lachlan Deck
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
40global-village.net
This email sent to email@hidden
--
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/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden