complicated query (newbie..)

Hi,

I've got 3 tables: hosts (with host.id column) and reservation (with
reservation.id column) in many-to-many relation, and reservation_hosts
which is an association table (with reservation_id and host_id columns).

So I've got this query which selects hosts and reservations under
certain conditions:

SELECT *
FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON
hosts.id = reservation_hosts_1.host_id

LEFT OUTER JOIN
reservation
ON
reservation.id = reservation_hosts_1.reservation_id

INNER JOIN
(SELECT rh.host_id, MIN(r.start_date) FROM reservation AS r INNER JOIN
reservation_hosts AS rh ON (r.id=rh.reservation_id AND ( (r.end_date <=
2009-04-10 AND r.start_date < 2009-04-09) OR (r.start_date = 2009-04-09
AND r.end_date <= 2009-04-10) OR r.start_date > 2009-04-09 )) GROUP BY
rh.host_id) AS min_date(host_id, start_date)
ON
hosts.id = min_date.host_id AND reservation.start_date =
min_date.start_date

ORDER BY hosts.id, reservation.start_date

Great. But I need to add to this table *hosts which have no reservations
at all* as well.

If I change INNER JOIN to LEFT OUTER JOIN, it adds the hosts I need, but
it also lists every reservation, not just those from the subquery.

Do you need a MIN(start_date) for each host you get from the query
before last join?
I think you can solve this with sub-select like this:

select
hosts.*, reservation_hosts.*, reservation.*,
(select MIN(r.start_date) FROM reservation AS r
INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id
where rh.host_id = hosts.id and /*date condition here*/)
FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON
hosts.id = reservation_hosts_1.host_id

LEFT OUTER JOIN
reservation
ON
reservation.id = reservation_hosts_1.reservation_id
ORDER BY hosts.id, reservation.start_date

Note: sub-select must return exactly one row!

--
Aurimas

Hello Aurimas,

Thanks for answer!

Do you need a MIN(start_date) for each host you get from the query
before last join?

Yes, I really do - the idea is that from several reservations fulfilling
the dates condition the earliest reservation has to be selected (i.e.
the one with minimum start date).

I edited your code slightly to allow for changed column names and
missing 'hosts' table in the subquery (there were syntax errors otherwise):

select
hosts.*, reservation_hosts.*, reservation.*,
(select MIN(r.start_date) FROM hosts, reservation AS r
INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id
where rh.host_id = hosts.id )
FROM hosts LEFT OUTER JOIN reservation_hosts ON
hosts.id = reservation_hosts.host_id

LEFT OUTER JOIN
reservation
ON
reservation.id = reservation_hosts.reservation_id
ORDER BY hosts.id, reservation.start_date

But it still doesn't work, i.e. it produces every host/reservation
combination (on top of listing hosts with no reservations and NULL in
place of reservation_id, which is fine).

I checked that subquery does indeed return exactly one row, although I'm
not sure why this has meaning.

Regards,
mk

On Thu, Apr 09, 2009 at 04:47:32PM +0200, Marcin Krol wrote:

I've got 3 tables: hosts (with host.id column) and reservation (with
reservation.id column) in many-to-many relation, and reservation_hosts
which is an association table (with reservation_id and host_id columns).

So I've got this query which selects hosts and reservations under
certain conditions:

If you could describe what you want in words it would help more. I
think you want something like "I was a list of all hosts and their first
reservation that doesn't cover some specific date".

If that's correct; you've got a couple of choices, either turn the inner
join into an outer join and move it up to join onto the hosts, or get
rid of it completely and use the DISTINCT ON clause.

--
Sam http://samason.me.uk/

Sam Mason wrote:

On Thu, Apr 09, 2009 at 04:47:32PM +0200, Marcin Krol wrote:

I've got 3 tables: hosts (with host.id column) and reservation (with
reservation.id column) in many-to-many relation, and reservation_hosts
which is an association table (with reservation_id and host_id columns).

So I've got this query which selects hosts and reservations under
certain conditions:

If you could describe what you want in words it would help more. I
think you want something like "I was a list of all hosts and their first
reservation that doesn't cover some specific date".

It's somewhat complicated:

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

If that's correct; you've got a couple of choices, either turn the inner
join into an outer join and move it up to join onto the hosts, or get
rid of it completely and use the DISTINCT ON clause.

I'll try doing smth with it..

Regards,
mk

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.

--
Sam http://samason.me.uk/

Hi,

Do you need a MIN(start_date) for each host you get from the query
before last join?

Yes, I really do - the idea is that from several reservations fulfilling
the dates condition the earliest reservation has to be selected (i.e.
the one with minimum start date).

I edited your code slightly to allow for changed column names and
missing 'hosts' table in the subquery (there were syntax errors otherwise):

The hosts table was not missing in the subquery! It meant to take
host-id from "current row: of main select. That subquery should work
alone only by replacing host.id by constant value.

select
hosts.*, reservation_hosts.*, reservation.*,
(select MIN(r.start_date) FROM hosts, reservation AS r
INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id
where rh.host_id = hosts.id )
FROM hosts LEFT OUTER JOIN reservation_hosts ON
hosts.id = reservation_hosts.host_id

LEFT OUTER JOIN
reservation
ON
reservation.id = reservation_hosts.reservation_id
ORDER BY hosts.id, reservation.start_date

But it still doesn't work, i.e. it produces every host/reservation
combination (on top of listing hosts with no reservations and NULL in
place of reservation_id, which is fine).

Check the main select without the subquery. Does it return the rows you
want? If not - its wrong!
If yes, than choose *any* host id from main select's result and write a
query, that would return a min(start_date) for *that* host. That query
should not need hosts table at all since you have a constant host id.
Now just place the second query as subquery into the first one,
replacing a constant host id by hosts.id. It should work.

I checked that subquery does indeed return exactly one row, although I'm
not sure why this has meaning.

I was a bit wrong. Subquery must return 0 or 1 row, but NOT MORE.

I hope it's clear now.

--
Aurimas

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.

On 09/04/2009 18:03, Marcin Krol wrote:

- 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.

I'd imagine this is the same problem as in your other post - if you
don't quote the dates, PG thinks each is an integer expression. The
error says it's looking for an OVERLAPS function that takes two dates
and two integers, which of course doesn't exist.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Thu, Apr 09, 2009 at 07:03:55PM +0200, Marcin Krol wrote:

- 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)

You *need* those quotes in there; you need to figure out what's going on
there first before going any further. Maybe the reservation dates for
that entry are "confused" for some reason, or you've got the date in the
wrong format or something (i.e. you're expecting dd/mm/yyyy and you're
getting mmm/dd/yyyy or something else).

- 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.

This is a big hint that things are going wrong. You need those quotes
in there, an "integer" is a plain number and not a date.

--
Sam http://samason.me.uk/

Sam Mason wrote:

This is a big hint that things are going wrong. You need those quotes
in there, an "integer" is a plain number and not a date.

This one does work in the sense of selecting out the wrong host but it
still produces nothing but NULLs!

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
('2009-04-09'::date,'2009-04-10'::date))
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
('2009-04-09'::date,'2009-04-10'::date)
ORDER BY h.id, r.start_date)

Regards,
mk

could provide greater assistance if you could post the database schema you're using

cheers (from across the pond)

Martin
GMT+5(this week)
______________________________________________
Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

Date: Thu, 9 Apr 2009 18:08:04 +0200
From: mrkafk@gmail.com
To: sam@samason.me.uk
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] complicated query (newbie..)

Sam Mason wrote:

On Thu, Apr 09, 2009 at 04:47:32PM +0200, Marcin Krol wrote:

I've got 3 tables: hosts (with host.id column) and reservation (with
reservation.id column) in many-to-many relation, and reservation_hosts
which is an association table (with reservation_id and host_id columns).

So I've got this query which selects hosts and reservations under
certain conditions:

If you could describe what you want in words it would help more. I
think you want something like "I was a list of all hosts and their first
reservation that doesn't cover some specific date".

It's somewhat complicated:

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

If that's correct; you've got a couple of choices, either turn the inner
join into an outer join and move it up to join onto the hosts, or get
rid of it completely and use the DISTINCT ON clause.

I'll try doing smth with it..

Regards,
mk

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
Rediscover Hotmail®: Get e-mail storage that grows with you.
http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009

On Thu, Apr 09, 2009 at 07:25:42PM +0200, Marcin Krol wrote:

Sam Mason wrote:

This is a big hint that things are going wrong. You need those quotes
in there, an "integer" is a plain number and not a date.

This one does work in the sense of selecting out the wrong host but it
still produces nothing but NULLs!

Yes, it would do.

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 ('2009-04-09'::date,'2009-04-10'::date))

The dates here are the date range that you want to give to the user; I
was assuming that just because somebody doesn't have a reservation at
the moment you still don't want to put reservations going backwards and
forwards to infinity.

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 ('2009-04-09'::date,'2009-04-10'::date)
ORDER BY h.id, r.start_date)

these dates are OK.

As a minor point, you shouldn't need to put the "::date" in unless
you're feeling pedantic, PG should figure that out for itself. I put
them in if I'm unsure of what's going on but most of my queries won't
have them in.

--
Sam http://samason.me.uk/

Martin Gainty wrote:

could provide greater assistance if you could post the database schema
you're using

Not sure what you mean by schema (I'm really new to DB world), if you
mean table descriptions from psql, here it is:

reservations=# \d hosts
Table "public.hosts"
Column | Type |
Modifiers
----------------------------+-------------------+----------------------------------------------------
id | integer | not null default
nextval('hosts_id_seq'::regclass)
ip | character varying |
hostname | character varying |
location | character varying |
architecture_id | integer |
os_kind_id | integer |
os_version_id | integer |
additional_info | character varying |
column_12 | character varying |
column_13 | character varying |
username | character varying |
password | character varying |
alias | character varying |
virtualization_id | integer |
shareable | boolean |
shareable_between_projects | boolean |
notes | character varying |
cpu | character varying |
ram | character varying |
column_24 | character varying |
batch | character varying |
asset | character varying |
owner | character varying |
ssh_key_present | character varying |
machine_type_model | character varying |
mac_address_eth_0 | character varying |
physical_box | boolean |
up_n_running | boolean |
available | boolean |
project_id | integer |
Indexes:
"hosts_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"hosts_architecture_id_fkey" FOREIGN KEY (architecture_id)
REFERENCES architecture(id)
"hosts_os_kind_id_fkey" FOREIGN KEY (os_kind_id) REFERENCES os_kind(id)
"hosts_os_version_id_fkey" FOREIGN KEY (os_version_id) REFERENCES
os_version(id)
"hosts_project_id_fkey" FOREIGN KEY (project_id) REFERENCES project(id)
"hosts_virtualization_id_fkey" FOREIGN KEY (virtualization_id)
REFERENCES virtualization(id)

reservations=#
reservations=# \d reservation
Table "public.reservation"
Column | Type | Modifiers
--------------+-------------------+----------------------------------------------------------
id | integer | not null default
nextval('reservation_id_seq'::regclass)
start_date | date |
end_date | date |
status | character varying |
businessneed | character varying |
notetohwrep | character varying |
email_id | integer |
project_id | integer |
Indexes:
"reservation_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"reservation_email_id_fkey" FOREIGN KEY (email_id) REFERENCES email(id)
"reservation_project_id_fkey" FOREIGN KEY (project_id) REFERENCES
project(id)

reservations=#
reservations=# \d reservation_hosts
Table "public.reservation_hosts"
Column | Type | Modifiers
----------------+---------+-----------
reservation_id | integer |
host_id | integer |
Foreign-key constraints:
"reservation_hosts_host_id_fkey" FOREIGN KEY (host_id) REFERENCES
hosts(id)
"reservation_hosts_reservation_id_fkey" FOREIGN KEY
(reservation_id) REFERENCES reservation(id)

Sam Mason wrote:

On Thu, Apr 09, 2009 at 07:25:42PM +0200, Marcin Krol wrote:

Sam Mason wrote:

This is a big hint that things are going wrong. You need those quotes
in there, an "integer" is a plain number and not a date.

This one does work in the sense of selecting out the wrong host but it
still produces nothing but NULLs!

Yes, it would do.

Well it does for selecting hosts, but I also want to select the nearest
reservation using r.id like you specified in 'SELECT h.id, r.id,
r.start_date, r.end_date'. I can't do this if r.id is NULL.

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 ('2009-04-09'::date,'2009-04-10'::date))

The dates here are the date range that you want to give to the user; I
was assuming that just because somebody doesn't have a reservation at
the moment you still don't want to put reservations going backwards and
forwards to infinity.

Not backwards, but forward into some reasonable range, like 3 months (I
want the user to see the nearby reservation in future).

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 ('2009-04-09'::date,'2009-04-10'::date)
ORDER BY h.id, r.start_date)

these dates are OK.

As a minor point, you shouldn't need to put the "::date" in unless
you're feeling pedantic, PG should figure that out for itself. I put
them in if I'm unsure of what's going on but most of my queries won't
have them in.

Oops! My PG (ver 8.1) does need this ::date suffix!

Regards,
mk

On Thu, Apr 09, 2009 at 08:16:55PM +0200, Marcin Krol wrote:

Sam Mason wrote:

On Thu, Apr 09, 2009 at 07:25:42PM +0200, Marcin Krol wrote:

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
('2009-04-09'::date,'2009-04-10'::date))

The dates here are the date range that you want to give to the user; I
was assuming that just because somebody doesn't have a reservation at
the moment you still don't want to put reservations going backwards and
forwards to infinity.

Not backwards, but forward into some reasonable range, like 3 months (I
want the user to see the nearby reservation in future).

I'm not sure if this is a question or something else. If you're
wondering how to do this just use a range of ('2009-01-01'::date,
'2009-12-31'::date) to show all entries for this year.

--
Sam http://samason.me.uk/

Thanks a lot, Sam (and others), thanks to your help I managed to finally
produce the query I wanted.

Regards,
mk