item.ex

defmodule Tracker.Item do
  use Tracker.Web, :model
  alias Tracker.Repo
  import Ecto.Query

  schema "items" do
    field :name, :string
    field :score, :decimal, virtual: true
    has_many :events, Tracker.Event
  end
  
  # keyword syntax
  def with_score(queryable) do
    from i in queryable,
     left_join: e in assoc(i, :events),
      group_by: i.id,  
        select: %{ i | score: fragment("coalesce(sum(?), 0.0)", e.value) }
  end

  # Macro Syntax
  def with_score(queryable) do
    queryable
    |> join(:left, [i], e in assoc(i, :events))
    |> group_by([i], i.id)
    |> select([i, e], %{ i | score: fragment("coalesce(sum(?), 0)", e.value)} )    
  end
end

item_test.ex

defmodule Tracker.ItemTest do
  use Tracker.ModelCase

  alias Tracker.Item
  alias Tracker.Event
  
  alias Ecto.Date


  @date Ecto.Date.cast!("2017-01-20")
  
  describe "with_score" do
    test "an item with no saved events should have score 0" do      
      item = Repo.insert!(%Item{name: "foo", short_label: "foo"})
      item_with_score = Item 
        |> where([i], i.id == ^item.id)
        |> Item.with_score
        |> Repo.one      
      assert item_with_score.score == Decimal.new(0.0)
    end
    test "an item with one saved events should have that item's score" do      
      item = Repo.insert!(%Item{name: "foo", short_label: "foo"})
      event = Repo.insert!(%Event{item_id: item.id, date: @date, value: 2.5})
      item_with_score = Item 
        |> where([i], i.id == ^item.id)
        |> Item.with_score
        |> Repo.one      
      assert item_with_score.score == Decimal.new(2.5)
    end
  end
end