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.

Barragan

why not use guards? such as def posts_with_comments(approved, featured, some_var) do and def posts_with_comments(approved, featured, some_var) when is_nil?(approved) do and so on – Segalman

@Felipe-Skinner If I'm understanding you correctly, that might work but I'm trying to avoid the explosion of condition testing whether in guards or whereever. – Barragan

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

Barragan

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

Minoan

would guards work as well or macro is just a better approach to this? – Segalman

Thanks @mudasobwa although I haven't had much luck yet. I've only tried the second option with the reduce. I think there's a end) missing before the other ends and it should be defmacro, not defmacrop. After that it still won't compile when I try to use do_join(approved: approved). do_join(approved: approved) is not a valid query expression. I also tried a very simple macro which always simply returns [post_id: p.id] – Barragan

Sorry, I thought macrop was a typo but I now realize it means private macro and it needs to be in the file before using. But ... it still doesn't work. (Ecto.Query.CompileError) Enum.reduce( ... is not a valid query expression. I don't really need this so it's more of a learning exercise now but it would be useful to get the reduce version working for future use. – Barragan

Indeed, defmacrop should return the AST that is acceptable by query macro, that’s why Enum.reduce should not appear there. Instead, Enum.reduce should be accomplished on compilation stage. I have updated the answer. – Minoan

@mudasobwa, That still does not compile. I think there's a ) missing from the first end but after fixing that, it says syntax error before: post_id. It doesn't seem to like the post_id: atom. I appreciate your efforts but it's probably not worth spending more time on. I'm pretty happy with the solution using dynamic. – Barragan

Indeed, I have it fixed and checked. Now it should work. – Minoan

Sorry, it still doesn't work for me. I'm not sure why but I've pasted your macro code exactly and calling it in the join with do_join(approved: approved, featured: featured) but it gives {:featured, ^featured} is not a valid query expression on the line number with the join. – Barragan

OK, this is an Ecto glitch and honestly, I ran out of ideas on how to make it Ecto-friendly. In Elixir [{:key, :value}] and [key: :value] are 100% identical, Ecto somehow treats it differently. If you could provide MCVE I could try to make it work. – Minoan

No problem. Thanks for your help. It's an interesting challenge. If I find the time I'll put up a MCVE but it really is as I describe. A simple Post with a one to many relationship to Comment which has the two booleans. Nothing at all unusual. For now, the dynamic approach works fine. If I had found that first, I probably wouldn't have posted the question. – Barragan