• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Outer Join Question
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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.

  • Follow-Ups:
    • Re: Outer Join Question
      • From: Ricardo Strausz <email@hidden>
  • Prev by Date: Re: appserver comparison chart
  • Next by Date: Re: can webobjects pages show up correctly in both netscape and IE
  • Previous by thread: Re: Validation Problem 3
  • Next by thread: Re: Outer Join Question
  • Index(es):
    • Date
    • Thread