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: Chuck Hill <email@hidden>
- Date: Fri, 9 Nov 2007 09:56:19 -0800
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:
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