Mixing Scopes and Associations in Phoenix/Ecto

Question

In Rails, if I have the following setup:

class Post < ActiveRecord::Base
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post

  def self.approved
    where(approved: true)
  end
end

Then I can do something like this:

post = Post.find(100)
comments = post.comments.approved

to quickly get all the approved comments for the given Post.

How can I do something similar in Ecto?

defmodule MyApp.Post do
  use Ecto.Model

  schema "posts" do
    #columns omitted
    has_many :comments, MyApp.Comment
  end
end

defmodule MyApp.Comment do
  use Ecto.Model

  schema "comments" do
    #columns omitted
    belongs_to :post, MyApp.Post
  end
end

I've got the post with comments pre-loaded:

post = MyApp.Post
       |> MyApp.Repo.get(100)
       |> MyApp.Repo.preload(:comments)

I am not even sure where to start with the approved scope in MyApp.Comment.

Electrocardiograph

This blog post may point you in the right direction: blog.drewolson.org/composable-queries-ecto – Fairleigh

Answer 1

Preloads are allowed to receive queries. So you can filter associated comments like this.

post = 
  MyApp.Post
  |> Ecto.Query.preload(comments: ^MyApp.Comment.approved(MyApp.Comment))
  |> MyApp.Repo.get(100)

And in your Comment model

def approved(query) do
  from c in query,
  where: c.approved == true
end

Midgard

Answer 2

I do not think it is possible with the current version of Ecto. Preload does not allow for filtering. Alternative is to get the comments with a query:

(from comment in MyApp.Comment, 
  where: comment.post_id == ^post_id 
    and comment.approved == true,
select: comment) |> Repo.all

Witherspoon

Answer 3

I'm really late to this party but got some free time and figure an answer could help folks new to Elixir.

If you're coming from Ruby/Rails one thing to remember is that data is stateless in Elixir/Erlang because values are immutable. So we don't have a way to manipulate the post and load the comment into the data structure. We can achieve the same end result two ways:

#1 Return a new struct/map with comments merged into it

post_with_comments = %{post | comments: comments} # or Map.put(post, :comments, comments)

where comments is something like:

comments = MyApp.Repo.get_by(MyApp.Comment, where: post_id == ^post.id).

#2 Preload the data into the post data structure by building a query to grab it all at once. We can do this by passing queries into queries, see below.

defmodule MyApp.Post.Query do
  def approved_with_comments(id) do
    get_post(id) |> with_approval(true) |> with_comments()
  end

  def get_post(id) do
    from p in MyApp.Post, where: p.id == ^id
  end

  def with_approval(query, approval) do
    from q in query, where: approved == ^approval
  end

  def with_comments(query) do
    from q in query, preload: [:comments]
  end
end

Typically, you'll want to always preload associations as it's more efficient for the database. I personally love this behavior in Ecto because it forces you to not shoot yourself in the foot all over with N+1 queries or makes them very obvious to see.

You can make the interface a bit more ergonomic for something like the Query module above by using the same function name with pattern matching:

def query(query, :by_id, id), do: from q in query, where: q.id == ^id
def query(query, :by_approval, approval), do: # ....

You then will map reduce whatever your params are into a single query object which you then finally load with Repo.one or whatever suits your fancy.

Sturtevant