1

I have a PG (12) database with two tables:

RESERVABLES

id               uuid

RESERVATIONS

id               uuid
reservable_id    uuid
reserved_between tsrange    

Where reservations.reservable_id is a foreign key on reservables.

Now, given a tsrange (i.e. [2020-02-01 14:30, 2020-02-01 15:30]) I need to find a Reservable not reserved yet for that time interval. However, I have no idea how to proceed.

A big plus would be to find the Reservable with the most free time (fewer reservations) for that date.

0

This is my (untested) query:

SELECT p.id
FROM reservables AS p
   LEFT JOIN reservations AS c
      ON p.id = c.reservable_id
         AND NOT c.reserved_between
                 && tsrange('2020-02-01 14:30', '2020-02-01 15:30', '[]')
WHERE NOT EXISTS (SELECT 1 FROM reservations AS s
                  WHERE p.id = s.reservable_id
                  AND s.reserved_between
                      && tsrange('2020-02-01 14:30', '2020-02-01 15:30', '[]'))
GROUP BY p.id
ORDER BY count(c.id) NULLS FIRST
LIMIT 1;

The important part is the NOT EXISTS condition, the left outer join, GROUP BY and ORDER BY are just to collect the big plus. If the performance is not good enough, go for the simpler query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.