Hello Sam,
Thanks a million for reply! I'm so frustrated with this..
Sam Mason wrote:
On Thu, Apr 09, 2009 at 06:08:04PM +0200, Marcin Krol wrote:
What I'm trying to accomplish is producing list of hosts available
within a specified timeframe.
What I have is a table of hosts, table of reservations (containing id,
start_date and end_date) and an association table reservation_hosts.
I need a list of hosts, with accompanying reservations fulfilling
certain (date-related) conditions.
But there are two twists:
- if host has reservation(s), but those do not fulfill the date
conditions (the host is not available within a specified timeframe), the
host obviously should NOT be listed
- if host has no reservations at all, it obviously is available, so it
should be listed
I think the following should do what you want.
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id
AND (r.start_date,r.end_date) OVERLAPS (${window_start},${window_end})
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND (r.start_date,r.end_date) OVERLAPS (${requested_start},${requested_end})
ORDER BY h.id, r.start_date)
The formatting is somewhat grim, but I think it should do what you want.
Well it almost works: I see that it selects out the host ids whose date
conditions are not met (while adding those that have no reservations),
but why it produces nothing but NULLs in place of values, even for hosts
who do have reservations but ones ?!
id id start_date end_date
4
NULL NULL NULL
5
NULL NULL NULL
6
NULL NULL NULL
7
NULL NULL NULL
8
NULL NULL NULL
9
NULL NULL NULL
10
NULL NULL NULL
11
NULL NULL NULL
12
NULL NULL NULL
13
NULL NULL NULL
I had to edit it a bit: it seems there was one parentheses missing after
first subquery:
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id AND r.start_date > 2009-04-09)
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND r.start_date > 2009-04-09
ORDER BY h.id, r.start_date)
Two things:
- If I quote date values like '2009-04-09' it doesn't work again! I.e.
result set includes one host id that should have been excluded (bc it
has reservation whose date doesn't match the condition)
- I have replaced OVERLAPS with explicit date condition bc PG complained:
ERROR: function pg_catalog.overlaps(date, date, integer, integer) does
not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
