Outer Join Question
Outer Join Question
- Subject: Outer Join Question
- From: email@hidden
- Date: Tue, 29 Apr 2003 11:08:15 -0400
Hi,
I am not much familiar with SQL and I need help to make my
EOFetchSpecification work.
I have Person entities that offer Service entities, for which
reservations can be arranged. When I create reservations, I update an
Availability table so I can later found available Person for a given
service at a given date. Each Availability record gives the number of
seats available for a particular date and service. As availabilities
are set only when a reservation is arranged, so I need to find all
Person entities that have the needed number of seats available for a
given date as well as all Person entities that have no Availability
record for that date. The latter Persons have no reservation at all for
that day, so they are fully available and should be fetched.
Here is the detail of my entities. I only include relevant attributes.
Person
NSArray services: services offered by the person
Service
Number dayOfWeek: the day of the week (1=Sunday, 2=Monday, etc) for
which the service is offered
Number startTime: the start time of the availability period for that
day of week in millisecond since midnight
Number closeTime: the end time of the availability period in
millisecond since midnight
Person person: the inverse ralationship of Person.services
NSArray availabilities: the inverse relationship of
Availability.service (not a class property)
Availability
NSTimestamp date: the date (without time) for which the availability
is computed
Number seatCount: the number of available seats for that date
Service service: the service for which the availability is computed
I have defined my Service.availabilities relation as an outer left join
so I can also fetch all Persons that have no corresponding Availability
record for the given date.
Here is the code that I use to make the request:
// assume the following
NSTimestamp date = the date to look for, with hours to millisecond set
to zero;
Integer dayOfWeek = the day of the week to look for;
Integer time = the time to look for;
Integer partySize = the number of seat to look for;
// find available Persons for given date, time and party size
EOQualifier qualifier =
EOQualifier.qualifierWithQualifierFormat("services.dayOfWeek = %@ AND
services.startTime <= %@ AND services.closeTime >= %@ AND
services.availabilities.date = %@ AND services.availabilities.seatCount
>= %@", new NSArray(new Object[]{dayOfWeek, time, time, date,
partySize, partySizeSelection}));
EOFetchSpecification fetchSpecification = new
EOFetchSpecification("Person", qualifier, null);
persons =
editingContext.objectsWithFetchSpecification(fetchSpecification);
This request should find two persons. One have an availability record
for the date I look for, and the other have a service for that time
period, but no availability record. However, the generated SQL only
finds one Person, the one with the availability record. It looks like
the outer join does not work. My database is OpenBase, which is
documented to support left outer joins. Here is the SQL from my app's
log:
SELECT t0.ADDRESS1, t0.ADDRESS2, t0.CITY_ID, t0.CODE, t0.CORNER_STREET,
t0.CREATION_TIME, t0.DESCRIPTION_ENGLISH, t0.DESCRIPTION_FRENCH,
t0.DISTRICT_ID, t0.EMAIL, t0.FAX, t0.FLOOR_PLAN, t0.FREE_PHONE,
t0.LANGUAGE, t0.LAST_ACCESS_TIME, t0.LAST_MODIFICATION_TIME, t0.NAME,
t0.PASSWORD, t0.PERSON_ID, t0.PHONE, t0.POSTAL_CODE, t0.PRICE_RANGE,
t0.SUBWAY_ID, t0.WEB_PAGE
FROM T_RESTAURANT t0, T_AVAILABILITY T2, T_SERVICE T1
WHERE (T1.DAY_OF_WEEK = ? AND T1.START_TIME <= ? AND T1.CLOSE_TIME >= ?
AND T2.DATE = ? AND T2.SEAT_COUNT >= ?) AND T1.SERVICE_ID *
T2.SERVICE_ID AND t0.PERSON_ID = T1.PERSON_ID
withBindings: 1:4(dayOfWeek), 2:68400000(startTime),
3:68400000(closeTime), 4:2003-04-30 00:00:00(date), 5:3(seatCount)
Thanks,
Serge
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.