Ecto join with dynamically built conditions

Question

I'm trying to build an Ecto query with a left join with optional extra conditions on the join. I'll try to describe it with the typical posts and comments example.

Post has_many Comments Comment belongs_to Post.

Let's say Comment has two boolean fields, approved and featured.

I want to get all Posts regardless of whether they have comments or not, hence the left join. I want comments preloaded but preferably one SQL query. I want to optionally filter comments on approved and featured.

I'm trying to write a function something like this where if approved or featured is not nil, they will be included in the join, if they are nil, they will be ignored. I haven't found a better way than something like this:

def posts_with_comments(approved, featured, some_var) do
  query = Post
  |> where([p], p.some_field == ^some_var

  cond do
    !is_nil(approved) and !is_nil(featured)
      -> join(query, :left, [p], c in Comment, [post_id: p.id, approved: ^approved, featured: ^featured])

    !is_nil(approved)
      -> join(query, :left, [p], c in Comment, [post_id: p.id, approved: ^approved])

    !is_nil(featured)
      -> join(query, :left, [p], c in Comment, [post_id: p.id, featured: ^featured])

    true -> join(query, :left, [p], c in Comment, [post_id: p.id])
  end

  |> preload([p, c], [comments: c])
  |> select([p], p)
  |> Repo.all

end

That works but there must be a better way. It would get crazy if I had a third parameter. I'm looking for a way to dynamically build that list for the on parameter of join(). My attempts to do that have failed because of the requirement to pin.

I can't put those conditions in a where because if I do something like where t.approved == true I get only posts approved comments.

Answer 1

I think the answer is to use the dynamic function.

This works. (leaving out the some_var condition I had earlier).

def posts_with_comments(approved, featured) do
  query = Post
  join(query, :left, [p], c in Comment, ^do_join(approved, featured))
  |> preload([p, c], [comments: c])
  |> Repo.all
end

defp do_join(approved, featured) do
  dynamic = dynamic([p, c], c.post_id == p.id)

  dynamic =
  case approved do
    nil -> dynamic
    _ -> dynamic([p, c], ^dynamic and c.approved == ^approved)
  end

  case featured do
    nil -> dynamic
    _ -> dynamic([p, c], ^dynamic and c.featured == ^featured)
  end
end

That's much better than my first attempt because it's a simple concatenation that just gets longer with more conditions rather than an explosion of conditions.

As an exercise I have been unable to make this more generic by feeding it a list of fields and using something like reduce. The problem I had there was making the field name (e.g., c.approved) work from a variable.

join seems to support two types of on parameters. The keyword list (which I assume implies ==) and the more expressive format. dynamic does not seem to work with the keyword list. It tries to expand p.id to p.id().

I couldn't get @mudasobwa's macro based solutions to work. I'm not exactly a macro expert yet but I don't see how the nil match can work at run time.

One more thing about the macro solution. For some reason, it doesn't work with the keyword list either. I would expect a bare bones macro like this to work:

defmacrop do_join do
  quote do
    [post_id: p.id]
  end
end

But it doesn't. It tries to expand p.id to p.id()

Answer 2

I would go with declaring a helper and pattern match arguments in it:

def posts_with_comments(approved, featured, some_var) do
  query = Post
          |> where([p], p.some_field == ^some_var)
          |> join(:left, [p], c in Comment, do_join(approved, featured))
          |> preload([p, c], [comments: c])
          |> select([p], p)
          |> Repo.all
end

defmacrop do_join(nil, nil) do
  quote do: [post_id: p.id]
end
defmacrop do_join(approved, nil) do
  quote bind_quoted: [approved: approved] do
    [post_id: p.id, approved: ^approved]
  end
end
defmacrop do_join(nil, featured) do
  quote bind_quoted: [featured: featured] do
    [post_id: p.id, featured: ^featured]
  end
end
defmacrop do_join(approved, featured) do
  quote bind_quoted: [approved: approved, featured: featured] do
    [post_id: p.id, approved: ^approved, featured: ^featured]
  end
end

defmacro is necessary to allow pin operators out of context.

Or, alternatively, Enum.reduce/3 it:

# kw is approved: approved, featured: featured
defmacrop do_join(kw) do
  initial = [{:post_id, {{:., [], [{:p, [], Elixir}, :id]}, [], []}}]
  Enum.reduce(kw, initial, fn
    {_, nil}, acc -> acc
    {k, _}, acc ->
      quoted = {k, {:^, [], [{k, [], Elixir}]}}
      [quoted | acc]
  end)
end