Best practice - ecto queries with aggregated values

Phoenix Forum Questions / Help
1

I am writing a simple web application.
In many cases, I need to load some entries from the DB as well as some aggregated fields(like sum and count).
I am wondering which is a more efficient way to deal with such cases,
the options I came up with are:

  1. Add these values as virtual fields to my schema’s and merge them into the selected fields-
    pros:

  • performance - getting all the data in one query.

  • Reliability of information - all information is obtained by atomic operation in DB.

    cons:

  • Adding fields to the schema whose meaning deviates from the essence of the entity (like query_count, totals_sum, etc).

  • The aggregated values will be returned spread out on all entities instead of getting one value

  1. Run two separate queries, one to retrieve the data and another one to retrieve aggregated values (pros and cons are the opposite of the ones in option 1.)

example:
option 1:

schema "product" do
 field :id, :string
 field :price :integer
 field :query_total, :integer, virtual: true
end
def get_data(query) do
   query
   |> select_merge([p], %{query_total: fragment("count(*) OVER()")})
   |> Repo.all()
end

option 2:

schema "product" do
 field :id, :string
 field :price :integer
end
def get_data(query) do
   query
   |> Repo.all()
end

def get_additional_data(query) do
   query
   |> exclude(:limit)
   |> exclude(:offset)
   |> select([cr], fragment("count(*) OVER()"))
   |> Repo.one()
end
2

Option 3: Run one query, which separately returns your struct and the aggregated value(s).

1 Like