"Clean" SQL (Avoid multiple reference to a table)
"Clean" SQL (Avoid multiple reference to a table)
- Subject: "Clean" SQL (Avoid multiple reference to a table)
- From: John Kuczynski <email@hidden>
- Date: Fri, 04 May 2007 21:15:16 -0400
Is there any way to influence EOF to generate better SQL (i.e. no
repetition of a Table name when doing a many-many join)?
Are there any 3rd party libraries that fix what's described below?)
Thanks,
-John
----------------
I have a many-many relationship between Users and Groups:
USER <<--- USERGROUP --->> GROUP
i.e. A User owns 1..n Groups
i.e. A Groups owns 1..n Users
It's modeled properly in EOModeler, and the relationships are flattened...
If I do a fetch similar to this:
NSMutableArray qualfiers = new NSMutableArray();
EOKeyValueQualifier qualifier1 = new EOKeyValueQualifier( "ownedGroups.ownerUser", EOQualifier.QualifierOperatorEqual, user );
qualifiers.add( qualifier1 );
EOKeyValueQualifier qualifier2 = new EOKeyValueQualifier( "ownedGroups.users", EOQualifier.QualifierOperatorEqual, otherUser );
qualifiers.add( qualifier2 );
EOAndQualifier andQualifier = new EOAndQualfier( qualifiers );
EOFetchSpecification fs = new EOFetchSpecification("User", andQualifier, null);
return ec.objectsWithFetchSpecification(fs);
This SQL that's generated: (Notice the two references to User)
SELECT t0.*
FROM `USER` t0, `GROUP` T1, `USER` T3, USERGROUP T2
WHERE (T3.recordid = '785884EF-1D87-8673-F3A5-2FB2966024B6' AND
t0.recordid = '8A180AA3-ED55-BC3D-BE41-18DFB128A580')
AND t0.recordid = T1.owneruserid AND T2.userid = T3.recordid
AND T1.recordid = T2.groupid
Obviously not the most refined version of the query... written by hand
we came up with this:
select t0.*
from user u, `group` g, usergroup ug
where u.recordid = '8A180AA3-ED55-BC3D-BE41-18DFB128A580'
and g.owneruserid = u.recordid
and g.recordid = ug.groupid
and ug.userid = '785884EF-1D87-8673-F3A5-2FB2966024B6'
_______________________________________________
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