Get availability query

Questions / Help
,
1

Hi guys

I have 3 table which is

offices that has_many meeting_rooms
meeting_rooms that has_many bookings

meeting_rooms
office_id FK to offices

bookings
meeting_room_id FK to meeting_rooms

I managed to get only the booked meeting_rooms by office_id within start_time and end_time

def list_booked_meeting_rooms(office_id, start_time, end_time) do
    query = from office in Office,
            where: office.id == ^office_id,
            join: meeting_rooms in assoc(office, :meeting_rooms),
            join: bookings in assoc(meeting_rooms, :bookings),
            where: bookings.start_time < ^end_time and ^start_time < bookings.end_time,
            select: meeting_rooms
    Repo.all(query)
end

Now I need to figure out how to get a list of available meeting rooms within the start_time and end_time which the reverse of list_booked_meeting_rooms where it will return a list of unbooked meeting_rooms

I tried doing it this way, but it does not work obviously. I’m not sure how to get the correct result :confused:

def list_available_meeting_rooms(office_id, start_time, end_time) do
    query = from office in Office,
            where: office.id == ^office_id,
            join: meeting_rooms in assoc(office, :meeting_rooms),
            join: bookings in assoc(meeting_rooms, :bookings),
            where: not (bookings.start_time < ^end_time and ^start_time < bookings.end_time) and is_nil(bookings),
            select: meeting_rooms
    Repo.all(query)
end

Thanks in advance

2

I didn’t run the code, but I think the idea should be like this:

from meeting_rooms in MeetingRoom,
  left_join: bookings in assoc(meeting_rooms, :bookings),
  where: meeting_rooms.office_id == ^office_id,
  where: is_nil(bookings.id) or not fragment("(?, ?) OVERLAPS (?, ?)", bookings.start_time, booking.end_time, ^start_time, ^end_time),
  distinct: true
3

Thanks for your suggestion.

I tried it out, but it did not work because of the or in the where statement

is_nil(bookings.id)

this statement will only be true if there no booking has been made

not fragment("(?, ?) OVERLAPS (?, ?)", bookings.start_time, booking.end_time, ^start_time, ^end_time)

this statement will return true if there is a past booking or future booking
btw, bookings.start_time < ^end_time and ^start_time < bookings.end_time return the same result as OVERLAPS. you can check here :wink:

the condition should be where meeting_room do not have booking at start_time and end_time period but I’m not sure how to build the query :confused:

4 
is_nil(bookings.id)

this statement will only be true if there no booking has been made

And it means that the room is available, isn’t it?

5

Keep in mind that this kind of query is subject to race conditions. Two clients could make this query, and the time span would show as open, and then both can attempt to reserve the spot. You’ll want to make sure that the part of the code that actually make the reservation serializes access to that timeslot.

6

That’s true. But it only cater for case if there’s no booking.
So if there’s an existing booking in the future or in the past this won’t work.
Solve one case through. Progress!

7

I managed to get the list of available meeting_room by changing the query a bit

from office in Office,
where: office.id == ^office_id,
join: meeting_rooms in assoc(office, :meeting_rooms),
where: fragment("NOT EXISTS (SELECT * FROM bookings WHERE start_time < ? AND ? < end_time)", ^selected_end_time, ^selected_start_time),
select: meeting_rooms

It worked when there is no booking at selected_end_time and selected_start_time which returns 2 available meeting_rooms

When creating a booking at selected_end_time and selected_start_time, it will return empty which is wrong and should return 1 available meeting_rooms
Not sure what went wrong :confused: