我有模型Subscription，其has_many Version秒。
AVersion有一個status,plan_id和authorized_at日期。
對 a 所做的任何更改Subscription都來自Version更新其父級的修改Subscription。
我們的目標是找到每個認購的Version具有最早authorized_at日期WHERE將versions.plan_id是一樣的subscriptions.plan_id（換句話說，我需要的授權日起Version在plan_id更改為當前Subscription的plan_id）。
這是我提出的查詢。我在聚合函式語法中遇到錯誤：
syntax error at or near "MIN" LINE 3: MIN (authorized_at) from versions ^
詢問：
select subscriptions.id,
MIN (authorized_at) from versions
where versions.plan_id = subscriptions.plan_id
) as current_version
from subscriptions
join versions on subscriptions.id = versions.subscription_id
where versions.status = 'processed'
我也不確定是否應該將版本分組plan_id，然后從每個組中挑選。我有點失落。
uj5u.com熱心網友回復：
您可以使用橫向子查詢，最好將其描述為 SQL 中的 foreach 回圈。它們是從單個相關記錄中選擇列或什至從一組相關記錄中選擇列的極其高效的方式。
對于訂閱中的每一行，資料庫將從authorized_at 訂購的版本中選擇一行：
SELECT "subscriptions".*,
"latest_version"."authorized_at" AS current_version,
"latest_version"."id" AS current_version_id -- could be very useful
FROM "subscriptions"
LATERAL
(
SELECT "versions"."authorized_at", "versions"."id"
FROM "versions"
WHERE "versions"."subscription_id" = "subscriptions"."id" -- lateral reference
AND "versions"."plan_id" = "subscriptions"."plan_id"
AND "versions"."status" = 'processed'
ORDER BY "versions"."authorized_at" ASC
LIMIT 1
) latest_version ON TRUE
在 ActiveRecord 中創建橫向連接可以使用 SQL 字串或 Arel 來完成：
class Subscription < ApplicationRecord
# Performs a lateral join and selects the
# authorized_at of the latest version
def self.with_current_version
lateral = Version.arel_table.then do |v|
v.project(
v[:authorized_at],
v[:id] # optional
).where(
v[:subscription_id].eq(arel_table[:id])
.and(v[:plan_id].eq(arel_table[:plan_id]) )
.and(v[:status].eq('processed'))
)
.order(v[:authorized_at].asc)
.take(1) # limit 1
.lateral('latest_version ON TRUE')
end
lv = Arel::Table.new(:latest_version) # just a table alias
select(
*where(nil).arel.projections, # selects everything previously selected
lv[:authorized_at].as("current_version"),
lv[:id].as("current_version_id") # optional
).joins(lateral.to_sql)
end
end
如果您只想選擇id和current_version列，您應該考慮使用 pluck 而不是選擇沒有正確水合的資料庫模型。
uj5u.com熱心網友回復：
您可以使用DISTINCT ON過濾掉行，并為每個訂閱保留一個 - 根據ORDER BY子句每個組的第一個。
例如：
select distinct on (s.id) s.id, v.authorized_at
from subscription s
join versions v on v.subscription_id = s.id and v.plan_id = s.plan_id
where v.status = 'processed'
order by s.id, v.authorized_at
uj5u.com熱心網友回復：
下面的代碼將為您提供versionsVersionplan_id等于 Subscription 的位置plan_id。
@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id")
按版本過濾記錄 status
@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id").where(status: "processed")
按版本過濾記錄status并按authorized_at升序排序。
@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id").where(status: "processed").order(:authorized_at)
按版本過濾記錄status并按authorized_at降序排序。
@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id").where(status: "processed").order(authorized_at: :desc)
希望這對你有用！
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/364929.html
標籤：sql 红宝石轨道 红宝石 数据库 PostgreSQL的