Redundant Results from Fetch to Single Table Inheritance Entity
Redundant Results from Fetch to Single Table Inheritance Entity
- Subject: Redundant Results from Fetch to Single Table Inheritance Entity
- From: "Jerry W. Walker" <email@hidden>
- Date: Thu, 08 Nov 2007 08:28:19 -0500
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