Order by virtual computed field

Question

Given the following schema, I want to compute :games_won / :games_played, populate it into :percentage_won and sort by :percentage_won. I managed to compute the value if I use select_merge and leave out "AS ?", but how do I reference this computed column in order_by?

schema "players" do
  field :name, :string
  field :games_played, :integer
  field :games_won, :integer

  field :percentage_won, :float, virtual: true

  timestamps()
end

I tried the following query:

def list_players(sort_by, sort_order) do
  query =
    from(p in Player,
      select_merge: %{percentage_won: fragment("(?::decimal / NULLIF(?,0)) AS ?", p.games_won, p.games_played, p.percentage_won)},
      order_by: [{^sort_order, field(p, ^String.to_atom(sort_by))}])
  Repo.all(query)
end

But calling list_players("percentage_won", :asc) gives me the following error:

** (Ecto.QueryError) ...:28: field `percentage_won` in `select` is a virtual field in schema Player in query:

from p0 in Player,
  order_by: [asc: p0.name],
  select: merge(p0, %{percentage_won: fragment("(?::decimal / NULLIF(?,0)) AS ?", p0.games_won, p0.games_played, p0.percentage_won)})

user_1437049 · Answer

Thiago Henrique already answered why virtual doesn't work here, but depending on your underlying database, I would like to present another solution: Generated Columns

Generated Columns are available in PostgreSQL since version 12 and allow to create columns, which are based on other columns values (Which fits your use-case very well!). You get all the advantages of your DB and there is no need to create a virtual field in your application layer.

To get it into the database, you can write a raw SQL migration, for example:

def up do
  execute """
    ALTER TABLE players ADD percentage_won numeric GENERATED ALWAYS AS (games_won::decimal / NULLIF(games_played,0)) STORED
  """
end

And your schema would look like this:

schema "players" do
  field :name, :string
  field :games_played, :integer
  field :games_won, :integer
  field :percentage_won, :float

  timestamps()
end

Everytime you insert/update one of the player rows, the new percentage_won value will be calculated and also inserted/updated. Now, you can also use this value in ecto queries like a normal column!

user_5036456 · Answer

TL;DR

You needs creates another Elixir function in your application to sort, using query will don't work.

Long explanation

Ecto.Query only creates database queries strings based in Elixir code. This queries will be executed in your database, so that your database needs know all columns specified in this query.

Since virtual fields only exist for your application, but not for your database, you only is enabled to sort data based in this field using your Elixir application, like bellow:

def list_players(sort_by, sort_order) do
 # ...

  Repo.all(query)
  |> order_result_manually()
end

user_2482108 · Answer

SQL (and ecto) support expressions in the order_by clauses as well, you just need to copy the expression from your select_merge to the order_by:

from(p in Player,
  select_merge: %{
    percentage_won: fragment("(?::decimal / NULLIF(?,0))", p.games_won, p.games_played)
  },
  order_by: [
    {^sort_order, fragment("(?::decimal / NULLIF(?,0))", p.games_won, p.games_played)}
  ])

Order by virtual computed field

3 Answers