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