A use-case for virtual fields in Ecto

Ever wanted to show a list of items along with the number of another related item, e.g. show a list of posts with the number of comments?

We can use Ecto virtual fields to achieve this.

Consider a schema defined as:

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

Now, consider we want to show a list of posts, with the number of comments each post has. We can preload comments relation and then show the number of comments, but that would load all the comments unnecessarily.

A solution can be to use a virtual field, for example, comments_count, like this:

schema "posts" do
  ...
  field :comments_count, :integer, virtual: true
  has_many :comments, Comment
  ...
end

Now we can load the posts and fill comments_count field in a query like this:

from p in Post,
  left_join: c in assoc(p, :comments),
  on: c.post_id == p.id,
  select: %{p | comments_count: count(c)},
  # or select_merge: %{comments_count: count(c)},
  group_by: p.id

With this query, we get our posts along with the number of comments in on query. Comments are not loaded, but their number is available, and our system also performs faster.

We can use this to load different kinds of relations in one go. Consider this schema:

schema "posts" do
  ...
  has_many :votes, Vote
  ...
end

schema "votes" do
  ...
  field :vote_up, :boolean
  ...
end

We want to show the number of upvotes/downvotes of a post. We could update post schema like this:

schema "posts" do
  ...
  field :voteup_count, :integer, virtual: true
  field :votedown_count, :integer, virtual: true
  has_many :votes, Vote
  ...
end

And use a query like this to fetch both types of votes:

vdown_query = from v in Vote, select: %{id: v.id, post_id: v.post_id}, where: v.vote_up == false
vup_query = from v in Vote, select: %{id: v.id, post_id: v.post_id}, where: v.vote_up == true

from p in Post,
  left_join: vdown in subquery(vdown_query),
  on: vdown.post_id == p.id,
  left_join: vup in subquery(vup_query),
  on: vup.post_id == p.id,
  select: %{
    p
    |
    votedown_count: count(vdown.id),
    voteup_count: count(vdown.id)
  },
  # or select_merge: %{votedown_count: count(vdown.id), voteup_count: count(vdown.id)}
  group_by: p.id

And we get both votedown_count and voteup_count filled, without loading all of the votes.

You can write functions in your context to load posts differently based on where you want to use them. Want to show posts with the number of comments on the home page? Write posts_with_comments_count function. Want to show a users' posts with number of upvotes/downvotes of each post? Write posts_with_votes_count function.

You can add more clauses to the above queries as you would usually do. Some things will not work as expected, since we're joining and also grouping, but that comes down to your knowledge of SQL and your database server and differs slightly between database servers.