Re: Redundant Results from Fetch to Single Table Inheritance Entity
Re: Redundant Results from Fetch to Single Table Inheritance Entity
- Subject: Re: Redundant Results from Fetch to Single Table Inheritance Entity
- From: "Jerry W. Walker" <email@hidden>
- Date: Sat, 10 Nov 2007 21:15:52 -0500
Hi, Chuck,
Thanks for taking a look. I did some Googling before either fixing
the problem or posting the issue on the list.
I'll try to put a simple example together this weekend and get it to
fail in the same way. Then I'll ship it off to Apple's Radar.
Regards,
Jerry
On Nov 9, 2007, at 12:56 PM, Chuck Hill wrote:
Thought about this for a while. The only explanation that I can
come up with is "a bug in EOF". I don't recall anyone having run
into this, but that does not mean much.
Chuck
On Nov 8, 2007, at 5:28 AM, Jerry W. Walker wrote:
Greetings,
I don't seem to be getting the correct SQL generated for a query
and I'm ending up with redundant results. I've fixed the problem
by adding an additional explicit EOQualifier to do the job that I
think that EOF should be doing, so this is no longer an immediate
problem. However, I'd like to throw the question out to the list
to determine if: 1) I'm doing anything wrong to cause the SQL to
be generated the way it is, or 2) whether there's a bug in EOF
that I should report (or have known about if already reported).
Environment:
WO 5.3
OpenBase 10.0
We have an EOModel with several entities, say, Student, School,
Teacher, etc. that have locations. The Location entity contains
address, city, state, postalCode, phone, and so forth. Using
single-table inheritance, We have several subentities for location
to match the entities being located, such as StudentLocation,
SchoolLocation, TeacherLocation, etc.
Each of the entities with locations has a one-to-many relationship
with the appropriate subentity for its locations, such as:
Student <->> StudentLocation
School <->> SchoolLocation
Teacher <->> TeacherLocation
The parent Location entity has a partyID attribute to hold the
foreign key for the to-one relationship back to the located entity
and, of course, a locationType attribute to distinguish among the
Location subentities.
We also use a State reference table/entity to hold the state's
full name (e.g. "New York") and the state's abbreviation (e.g. "NY").
To search for all male students in New York, NY with an
"Underwater Basketweaving" major, I use the following code to
build and execute the fetch:
static final NSArray preFetchKeyPaths = new NSArray(new Object[] {
"locations",
"locations.state",
"locations.zipCodeLocation"
});
...
EOQualifier qual;
NSMutableArray theQualifiers = new NSMutableArray();
qual = EOQualifier.qualifierWithQualifierFormat
("locations.state.abbreviation caseInsensitiveLike %s", new NSArray
(selectedStateAbbreviation));
theQualifiers.addObject(qual);
qual = EOQualifier.qualifierWithQualifierFormat("locations.city
caseInsensitiveLike %s", new NSArray(selectedCityName));
theQualifiers.addObject(qual);
qual = EOQualifier.qualifierWithQualifierFormat("major
caseInsensitiveLike %s", new NSArray(selectedMajor.name()));
theQualifiers.addObject(qual);
EOFetchSpecification fs = new EOFetchSpecification
("StudentRecord", new EOAndQualifier(theQualifiers), null);
LOGGER.debug("Get the DoctorRecords with qualifiers = " + new
EOAndQualifier(theQualifiers).toString());
fs.setPrefetchingRelationshipKeyPaths(preFetchKeyPaths);
filteredDoctors = ec.objectsWithFetchSpecification(fs);
The problem occurs if a Student happens to have the same address
as a Teacher or School. The SQL that's generated for the above
fetch is as follows:
====================================
[2007-11-06 09:33:14 EST] <WorkerThread1> evaluateExpression:
<com.webobjects.jdbcadaptor.OpenBasePlugIn$OpenBaseExpression:
"SELECT t0.MAJOR, t0.EMAIL_ADDRESS, t0.FIRST_NAME,
t0.LAST_NAME, ..., FROM STUDENT_RECORD t0, STATE T2, LOCATION T1
WHERE (UPPER(T2.ABBREVIATION) LIKE UPPER(?) AND UPPER(T1.CITY)
LIKE UPPER(?) AND UPPER(t0.MAJOR) LIKE UPPER(?)) AND T1.STATE_ID =
T2.OID AND t0.OID = T1.PARTY_ID" withBindings:
1:"NY"(abbreviation), 2:"New York"(city), 3:"Underwater
Basketweaving"(major)>
====================================
I don't understand why EOF doesn't automatically add the WHERE
clause: "t0.LOCATION_TYPE = ? AND ".
Notice the setPrefetchingRelationshipKeyPaths statement. That
causes the generation of the following SQL statements as well,
which EACH INCLUDE THE LOCATION_TYPE CLAUSE appropriately:
====================================
[2007-11-08 07:33:14 EST] <WorkerThread1> evaluateExpression:
<com.webobjects.jdbcadaptor.OpenBasePlugIn$OpenBaseExpression:
"SELECT t0.ADDRESS_1, t0.ADDRESS_2, t0.CITY, t0.PARTY_ID,
t0.LOCATION_TYPE, t0.OID, t0.PHONE_NUMBER, t0.POSTAL_CODE,
t0.STATE_ID, t0.ZIP_CODE_ID FROM LOCATION t0, STUDENT T1, STATE T2
WHERE (t0.LOCATION_TYPE = ? AND (UPPER(T1.MAJOR) LIKE UPPER(?) AND
UPPER(t0.CITY) LIKE UPPER(?) AND UPPER(T2.ABBREVIATION) LIKE UPPER
(?))) AND t0.PARTY_ID = T1.OID AND t0.STATE_ID = T2.OID"
withBindings: 1:6(locationType), 2:"Underwater
Basketweaving"(major), 3:"New York"(city), 4:"NY"(abbreviation)>
====================================
====================================
[2007-11-08 07:33:14 EST] <WorkerThread1> evaluateExpression:
<com.webobjects.jdbcadaptor.OpenBasePlugIn$OpenBaseExpression:
"SELECT DISTINCT t0.ABBREVIATION, t0.NAME, t0.OID FROM STATE t0,
LOCATION T1, STUDENT T2 WHERE ((UPPER(t0.ABBREVIATION) LIKE UPPER
(?) AND UPPER(T1.CITY) LIKE UPPER(?) AND UPPER(T2.MAJOR) LIKE UPPER
(?)) AND T1.LOCATION_TYPE = ?) AND t0.OID = T1.STATE_ID AND
T1.PARTY_ID = T2.OID" withBindings: 1:"NY"(abbreviation), 2:"New
York"(city), 3:"Underwater Basketweaving"(major), 4:6(locationType)>
====================================
====================================
[2007-11-08 07:33:14 EST] <WorkerThread1> evaluateExpression:
<com.webobjects.jdbcadaptor.OpenBasePlugIn$OpenBaseExpression:
"SELECT DISTINCT t0.LATITUDE, t0.LONGITUDE, t0.OID, t0.ZIP_CODE
FROM ZIP_CODE_LOCATION t0, STUDENT T3, LOCATION T1, STATE T2 WHERE
((UPPER(T2.ABBREVIATION) LIKE UPPER(?) AND UPPER(T1.CITY) LIKE
UPPER(?) AND UPPER(T3.MAJOR) LIKE UPPER(?)) AND T1.LOCATION_TYPE
= ?) AND T1.PARTY_ID = T3.OID AND t0.OID = T1.ZIP_CODE_ID AND
T1.STATE_ID = T2.OID" withBindings: 1:"NY"(abbreviation), 2:"New
York"(city), 3:"Underwater Basketweaving"(major), 4:6(locationType)>
====================================
To circumvent the problem, I've added an EOQualifier for
locationType explicitely, but I don't understand why I should have
had to do this.
Thanks in advance for any advice or suggestions.
Regards,
Jerry
--
__ Jerry W. Walker,
WebObjects Developer/Instructor for High Performance Industrial
Strength Internet Enabled Systems
email@hidden
203 278-4085 office
_______________________________________________
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