Ecto association with a condition

Question

Let's say I have two models, Post and Comment and the comment model can be 1 out of 2 types, normal and fancy which is defined by the column type in the comments table.

Now I want to add 2 associations on my Post model, where one refers to fancy comments and one to normal ones, how would I do it? So I want something like this:

has_many :fancy_comments, MyApp.Comment, where: [type: 0]
has_many :normal_comments, MyApp.Comment, where: [type: 1]

Answer 1

Until recently, this wasn't available in Ecto. The other answer to this question provides the current details. There was a lengthy discussion about how to add it this GitHub issue.

In older versions of Ecto, you could use a composable query for this:

defmodule MyApp.Comment do
  
  ...schema, etc.

  def fancy(query) do
    from c in query,
      where: type == 0
  end

  def normal(query) do
    from c in query,
      where: type == 1
  end    
end

You can then use has_many :comments, MyApp.Comment and query based on that:

assoc(post, :comments) |> Comment.fancy() |> Repo.all()

Here is a blog post about composable queries.

You can also use a preload with a query:

fancy_query = from(c in Comments, where: type == 0)
Repo.preload(post, comments: fancy_query)