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]

Bisitun

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)

Stig

Don't you happen to know if it's possible to implement and if it's planned to be implemented? Not exactly where but something for filtering in general? – Bisitun

I am sure it is possible to implement - github.com/elixir-lang/ecto/issues/659#issuecomment-121233468 would suggest that there are no plans for implementing it. The discussion moves towards using composable queries to achieve this like my first example. – Stig

Answer 2

Conditional associations are now available in Ecto: https://hexdocs.pm/ecto/Ecto.Schema.html#has_many/3-filtering-associations

defmodule Post do
  use Ecto.Schema

  schema "posts" do
    has_many :public_comments, Comment, where: [public: true]
  end
end

Roderich