Re: Outer Join Question
Re: Outer Join Question
- Subject: Re: Outer Join Question
- From: Ricardo Strausz <email@hidden>
- Date: Mon, 5 May 2003 15:20:24 -0500
In pseudo code, the kind of SQL you need is:
select * from Restaurant where restaurantOpen AND (seatsAvailables OR
services.availability = nil)
The "ease" way is to do it via RawSQL, but you may need to know a bit
more of SQL.
Other way to do it, is by code; set a usual inner relations S->R and
R->>A.
Fetch those Services with restaurantOpen like qualifier. (this most
bring you 2 restaurants, but via the to-one relation)
go thru all of them performing something like:
while(not(services.restaurant.availability) OR
(services.restaurant.availability.date = THEDATE AND
services.restaurant.availability.seatCount >= THESEATS))
services.restaurant.addToListOfGodies();
You rarely will nead something else from Inner relations...
Dino
p.s. try to replay allways to the list; there are more people who may
help to solve the problem. Good lock!
On viernes, mayo 2, 2003, at 14:09 America/Mexico_City, Serge Froment
wrote:
Hello Dino,
Restaurant is a subclass of Person, so consider them the same. Here is
a better description of the problem. All qualifiers below are looking
for Restaurant entities.
When we want to book a reservation for 3 people on Wednesday April 30
2003 at 7:00 PM, we have to find out restaurants that satisfy two
conditions: (1) the restaurant has to be open for business at that
time and (2) there must be at least 3 seats availables.
The purpose of the Service entity is to answer the first condition: Is
the restaurant open for business on Wednesday at 7:00 PM? So, I have
created the following fetch spec in EOModeler and called it
"restaurantOpen":
restaurantOpen: ((services.dayOfWeek = 4) and (services.startTime <=
68400000) and (services.closeTime >= 68400000))
This fetch spec retreives two restaurants: "Au Dragon Soyeux" and
"L'Est-ce ton minet". This is the correct result since both
restaurants are open for business every Wednesday from 6:00 PM
(service.startTime) to 9:00 PM (service.closeTime).
Then I introduced the Availability entity to help answer the second
condition: Is there at least 3 seats available on Wednesday April 30
2003 at 7:00 PM? To test that condition, I could do the following:
((services.availabilities.date = '2003-04-30') and
(services.availabilities.seatCount >= 3))
However, instance of Availability are created only when reservations
are booked for a specific date. So, in order to find out available
restaurants for April 30 at 7:00 PM, I should test for the above
condition OR any restaurant that has no Availability record for April
30 and the matching Service record. But how can I test if a restaurant
has no Availability record? There is no qualifier to test this
condition. So I use the Left Outer Join feature of EOModeler. The
WebObjects documentation explains that "A left outer join preserves
rows in the left (source) table, keeping them even if there's no
corresponding row in the right table" (see:
http://developer.apple.com/techpubs/webobjects/Reference/API/com/
webobjects/eoaccess/concepts/EORelationshipConcepts.html). So the
above fetch spec should work with Left Outer Join. In my EOModeler
model, the Service.availabilities relation is set to "Left Outer".
Then, the resulting fetch spec is:
seatsAvailables: ((services.availabilities.date = $date) and
(services.availabilities.seatCount >= 3))
bindings: date = 2003-04-30
Executing this qualifier should retreive both restaurants. "Au Dragon
Soyeux" is available because its Availability record for April 30 has
at least 3 seats. "L'Est-ce ton minet" has no Availability record for
April 30 and should therefore be found because of the Left Outer Join.
This does not work: only "Au Dragon Soyeux" is retreived. This is my
problem.
Finally, here is the actual fetch spec I use. It combines both
"restaurantOpen" and "seatsAvailables" conditions above.
((services.dayOfWeek = $dayOfWeek) and (services.startTime <= $time)
and (services.endTime >= $time) and (services.availabilities.date =
$date) and (services.availabilities.seatCount >= $seatCount))
bindings: date = 2003-04-30, dayOfWeek = 4, time = 68400000, seatCount
= 3
The resulting SQL from this fetch spec is:
SELECT ADDRESS1, ADDRESS2, CITY_ID, CODE, CORNER_STREET,
CREATION_TIME, DESCRIPTION_ENGLISH, DESCRIPTION_FRENCH, DISTRICT_ID,
EMAIL, FAX, FLOOR_PLAN, FREE_PHONE, LANGUAGE, LAST_ACCESS_TIME,
LAST_MODIFICATION_TIME, NAME, PASSWORD, PERSON_ID, PHONE, POSTAL_CODE,
PRICE_RANGE, SUBWAY_ID, WEB_PAGE FROM T_RESTAURANT WHERE (DAY_OF_WEEK
= ? AND START_TIME <= ? AND END_TIME >= ? AND DATE = ? AND SEAT_COUNT
>= ?) AND PERSON_ID = PERSON_ID and SERVICE_ID * SERVICE_ID
Serge Froment
_______________________________________________
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.