How to turn Ecto select queries into structs in Phoenix?

I've got two models, Song and Vote, where songs has many votes. I want to select all songs and count the number of votes for each.

The index action in the SongController, generated using the mix gen task, has been modified to this:

def index(conn, _params) do
  query = from s in Song, select: %{id: s.id, name: s.name, artist: s.artist} 
  songs = Repo.all(query)
  render(conn, "index.html", songs: songs)
end

In this case songs contains a list of lists. But in the orginal, generated function, songs = Repo.all(Song) it is a list of Song structs.

This means that the song_path functions in the template break with the following error message: maps cannot be converted to_param. A struct was expected, got: %{artist: "Stephen", id: 3, name: "Crossfire"}

Of course, what I really want to do is to somehow add a num_votes field to the select statement, and then somehow make a corresponding field to the Song struct?

Solution

First we should add a virtual field to the song schema so that it can be used to store the num_votes result:

defmodule Song do
  use Ecto.Schema

  schema "songs" do
    field :num_votes, :integer, virtual: true
    ...
  end
end

Using a combination of Ecto.Query.select/3, Ecto.Query.join/5 and Ecto.Query.API.count/1 we can add the counts to the map you are using to select from the query:

  query = from s in Song,
    left_join: v in assoc(:votes),
    select: %{id: s.id, name: s.name, artist: s.artist, num_votes: count(v.id)}

We can then use Kernel.struct to convert each item to a struct:

  songs =
    query
    |> Repo.all()
    |> Enum.map(fn(song) -> struct(Song, song) end)

This returns a list of song structs that can be used in the view.