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)})

Answer 1

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!