elixir - 按虚拟计算域排序

Question

鉴于以下架构，我想计算 :games_won/:games_played，将其填充到 :percentage_won 并按 :percentage_won 排序。如果我使用 select_merge 并省略“AS ?”，我就能计算出该值，但如何在 order_by 中引用此计算列？

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

  field :percentage_won, :float, virtual: true

  timestamps()
end

我尝试了以下查询:

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

但是调用 list_players("percentage_won", :asc) 会出现以下错误:

** (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 已经回答了为什么 virtual 在这里不起作用，但根据您的底层数据库，我想提出另一个解决方案:Generated Columns

生成列在 PostgreSQL 版本 12 中可用，并允许创建基于其他列值的列(这非常适合您的用例!)。您可以获得数据库的所有优势，并且无需在应用程序层中创建虚拟字段。

要将其放入数据库，您可以编写原始 SQL 迁移，例如:

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

您的架构将如下所示:

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

  timestamps()
end

每次插入/更新玩家行之一时，都会计算并插入/更新新的 percentage_won 值。现在，您还可以像普通列一样在 ecto 查询中使用此值!