How to query empty or nil association in elixir

Question

I'm trying to get a list of available meeting rooms by office_id where the availability is depending on the bookings with selected_start_time and selected_end_time

Here's my schema

defmodule MyApp.Context.Booking do
  schema "bookings" do
    field :start_time, :utc_datetime
    field :end_time, :utc_datetime
    belongs_to :meeting_room, MyApp.Context.MeetingRoom, foreign_key: :meeting_room_id
    timestamps()
  end
end

defmodule MyApp.Context.MettingRoom do
  schema "meeting_rooms" do
    belongs_to :office, MyApp.Context.Office, foreign_key: :office_id
    has_many :bookings, MyApp.Context.Booking
    timestamps()
  end
end

defmodule MyApp.Context.Office do
  schema "offices" do
    field :name, :string
    has_many :meeting_rooms, MyApp.Context.MeetingRoom
    timestamps()
  end
end

What I've already tried using fragment. It worked if there is no booking created for a selected time. After creating a booking for meeting_room_id = 1 it will show an empty list even though it still has another available meeting_room which is meeting_room_id = 2

def list_available_meeting_rooms(office_id, selected_start_time, selected_end_time) do
  query = 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
  Repo.all(query)
end

This statement will check for overlaping start_time and end_time. You can check here Algorithm to detect overlapping periods

fragment("NOT EXISTS (SELECT * FROM bookings WHERE start_time < ? AND ? < end_time)", ^selected_end_time, ^selected_start_time)

The expected result should return a list of available meeting_rooms For example:

offices
id | name 
1  | Office 1

meeting_rooms
id | office_id
1  | 1
2  | 1
3  | 1
4  | 1

bookings
id | start_time           | end_time             | meeting_room_id
1  | 2019-09-17 03:00:00Z | 2019-09-17 04:00:00Z | 1
2  | 2019-09-17 03:00:00Z | 2019-09-17 04:00:00Z | 2

When querying the list of available meeting rooms by office_id and start_time = 2019-09-17 03:00:00Z and end_time = 2019-09-17 04:00:00Z it should return only meeting_rooms id 3 and 4.

How do I achieve this kind of result?

Any kind of help would be great. Thanks in advance!

You should not change the question description with what was suggested in the answers to make future visitors be able to understand the whole picture. This site was not created to help _you_ in particular, it was created to help _people_ by being a knowledge base. If you change the description, the answers become obsolete. I have rolled changes back. — Aleksei Matiushkin, Sep 17 '19 at 04:21
Sorry about that. But you did not answer my question, you just improve what I already have. — Alooza, Sep 17 '19 at 06:31
You did not state _any_ question in the wall of text above. Question is somewhat ending with the question mark, at least in English. I pointed out to the first issue that could solve the problem, it did not (because you did not provide any test data I could test my solution on.) Now I have updated my answer with another suggestion. Also, if I did not answer the question, my answer would not get the green mark, so everything is fine. — Aleksei Matiushkin, Sep 17 '19 at 06:35
Updated my question with a question mark. How would you suggest me to provide test data? — Alooza, Sep 17 '19 at 06:50

Aleksei Matiushkin · Answer 1 · 2019-09-17T04:23:24.430

0

You select from Office which means you apply conditions on the Office while you probably want to apply conditions on meeting_rooms. AFAICT, you don’t need an Office at all, but you need to indeed join bookings since you use it in the fragment.

So somewhat like that should work.

query =
  from meeting_room in MeetingRoom,
  join: bookings in assoc(meeting_room, :bookings),
  where: meeting_room.office.id == ^office_id,
  where: fragment(...),
  select: meeting_room
Repo.all(query)

edited Sep 17 '19 at 04:23

answered Sep 16 '19 at 15:34

Aleksei Matiushkin

119,336
10
100
160

Correct. I edited the question. But the `fragment(...)` is not returning the expected result which it should return the list of `meeting_rooms` that is not booked at selected `start_time` and `end_time` – Alooza Sep 17 '19 at 01:45
I give it a try – Alooza Sep 17 '19 at 06:36

score 0 · Answer 2 · answered Sep 24 '19 at 09:31

I believe the answer by @aleksei-matiushkin is correct and I am not sure why it wouldn't work. But another way to reach the same result without using fragment is conditional join and then check for the null value, I haven't tested it but something along the lines of the following should do exactly the same:

MeetingRoom
|> join(:left, [mr], mrb in assoc(mr, :bookings), on:
  mrb.meeting_room_id == mr.id and 
  mrb.start_time < ^selected_end_time and 
  mrb.end_time > ^selected_start_time
)
|> where([mr], mr.office_id == ^office_id)
|> where([mr, mrb], is_nil(mrb.id))

How to query empty or nil association in elixir

2 Answers2