Getting a virtual field in Ecto calculated during a SQL Join Query

Question

This may be more of SQL question rather than an Elixir/Etco Question.

I have many to many relationships of User-Transactions-Merchant, where the user has many Merchants through Transactions and the Merchant has many customers through Transactions. That is pretty typical. I can get all of a merchant's customers through Ecto by doing something like this:

def find_merchant_customers(%{"merchant_id" => id}) do
  merchant = Repo.get(Merchant, id)
  Repo.all assoc(merchant, :customers)
end

If I want to find a balance for a user with a specific merchant, I have SQL query like this which sums up the transactions and produced a balance for that merchant.

def customer_balance(user_id: user_id, merchant_id: merchant_id) do
  q = from t in Transaction,
    select: fragment("SUM(CASE WHEN ? = 'credit' THEN (?) ELSE - (?) END)", t.type, t.amount, t.amount),
    where: t.user_id == ^user_id and t.merchant_id == ^merchant_id
  balance = Repo.one(q) || 0
  do_balance(balance, "asset")
   |> Money.new(:USD)
end

The question

How do I combine the two operations into one query such that the Join retrieves a list of users and populates the virtual attribute of Balance in the user. I know I could just run the first query and get the list of users then transform that data by retrieving each of the balances for each user, although that seems very inefficient. An alternative might be to understand how to assign the select fragement( to an attribute within the query as a sub-query. Any guidance would be helpful.

My User Model

defmodule MyApp.User do
  @moduledoc """
  User struct for user related data
  """
  import MyApp.Validation
  use MyApp.Model
  use Coherence.Schema

  schema "my_app_users" do
    field :email, :string
    field :first_name, :string
    field :last_name, :string
    field :role, :integer
    field :birthdate, Ecto.Date
    field :address1, :string
    field :address2, :string
    field :city, :string
    field :state, :string
    field :zip, :string
    field :status, :boolean, default: true
    field :verified_email, :boolean, default: false
    field :verified_phone, :boolean, default: false
    field :mobile, :string
    field :card, :string
    field :sms_code, :string
    field :balance, Money.Ecto, virtual: true
    field :points, :integer, virtual: true

    coherence_schema

    has_many :transactions, MyApp.Transaction
    has_many :merchants, through: [:transactions, :merchant]

    timestamps()
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:email, :first_name, :last_name, :password_hash, :role, :birthdate, :address1, :address2, :city, :state, :zip, :status, :mobile, :card, :sms_code, :status, :merchant_id, :verified_email, :verified_phone])
    |> validate_required_inclusion([:email, :mobile])
    |> validate_format(:email, ~r/(\w+)@([\w.]+)/)
  end

  defp put_password_hash(changeset) do
    case changeset do
      %Ecto.Changeset{valid?: true, changes: %{password: password}} ->
        put_change(changeset, :password_hash, Comeonin.Bcrypt.hashpwsalt(password))
      _ ->
        changeset
    end
  end

My Merchant Model

defmodule MyApp.Merchant do
  @moduledoc """
  Merchant Struct

  Merchant has an owner of a User - Which must exist
  """
  use MyApp.Model
  use Arc.Ecto.Schema

  schema "my_app_merchants" do
    field :name, :string
    field :email, :string
    field :address1, :string
    field :address2, :string
    field :city, :string
    field :zip, :string
    field :state, :string
    field :status, :boolean, default: true
    field :description, :string
    field :image, MyRewards.Avatar.Type
    field :phone, :string
    field :website, :string

    has_many :transactions, MyApp.Transaction
    has_many :customers, through: [:transactions, :user]

    timestamps()
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:name, :email, :address1, :address2, :city, :zip, :state, :status, :description, :phone, :website, :status, :category_id, :user_id])
    |> cast_attachments(params, [:image])
    |> validate_required([:name])
    |> validate_format(:email, ~r/(\w+)@([\w.]+)/)
  end
end

Query Functions

  def find_merchant_customers(%{"merchant_id" => id}) do
    merchant = Repo.get(Merchant, id)
    Repo.all assoc(merchant, :customers)
  end


  def customer_balance(user_id: user_id, merchant_id: merchant_id) do
    q = from t in Transaction,
        select: fragment("SUM(CASE WHEN ? = 'credit' THEN (?) ELSE - (?) END)", t.type, t.amount, t.amount),
        where: t.user_id == ^user_id and t.merchant_id == ^merchant_id
    balance = Repo.one(q) || 0
    do_balance(balance, "asset")
      |> Money.new(:USD)
  end

Answer 1

Move the fragment into a macro to keep the code clear:

  defmacro balance_amount(transaction) do
    quote do
      fragment("CASE WHEN ? = 'credit' THEN (?) ELSE - (?) END",
        unquote(transaction).type, unquote(transaction).amount, unquote(transaction).amount)
    end
  end

Create a subquery with %{user_id, merchant_id, balance}

  def user_merchant_balance do
    from t in Transaction,
    select: %{user_id: t.user_id, merchant_id: t.merchant_id, balance: sum(balance_amount(t))},
    group_by: [t.user_id, t.merchant_id]
  end

Join to the subquery from the main query, use the map update syntax %{|} to populate the virtual field:

  def merchant_customers(merchant_id) do
    from u in User,
    join: b in subquery(user_merchant_balance()), on: u.id == b.user_id,
    where: b.merchant_id == ^merchant_id,
    select: %{u | balance: b.balance}
  end

Edit: In Ecto 2.2, the balance field can be cast to Money.Ecto.Type

  def merchant_customers(merchant_id) do
    from u in User,
    join: b in subquery(user_merchant_balance()), on: u.id == b.user_id,
    where: b.merchant_id == ^merchant_id,
    select: %{u | balance: type(b.balance, Money.Ecto.Type)}
  end