我使用的查詢，以獲取SubscriptionCart與最早的authorized_at一個日期Subscription。
從 a 開始Subscription has_many SubscriptionCart，我使用與plan_idSubscription相同的過濾器過濾購物車plan_id。我也使用WHERE宣告Subscription和SubscriptionCart狀態。
該查詢似乎在控制臺上運行良好，但是當我將其放入訂閱范圍并嘗試在我的視圖中使用它時，我得到：
PG::SyntaxError: ERROR: syntax error at or near "WHERE"
LINE 6: ...ubscriptions.id, subscription_carts.authorized_at WHERE "sub...
到底是怎么回事？
詢問：
select distinct on (s.id) s.id, sc.authorized_at as cart_authorized_at, s.*
from subscriptions s
join subscription_carts sc on sc.subscription_id = s.id and sc.plan_id = s.plan_id
where sc.status = 'processed'
and s.status IN ('authorized','in_trial', 'paused')
order by s.id, sc.authorized_at
范圍：
scope :with_current_price_version, -> {
select("
DISTINCT ON (subscriptions.id) subscriptions.id,
subscription_carts.authorized_at as version_cart_authorized_at,
subscriptions.*
")
.joins("
INNER JOIN subscription_carts
ON subscription_carts.subscription_id = subscriptions.id
AND subscription_carts.plan_id = subscriptions.plan_id
WHERE subscription_carts.status = 'processed'
AND subscriptions.status IN ('authorized','in_trial', 'paused')
ORDER BY subscriptions.id, subscription_carts.authorized_at
")
}
編輯：將 WHERE、AND & ORDER BY 子句移到 .join 之外修復了該問題。
更新的范圍看起來像：
scope :with_current_price_version, -> {
select("DISTINCT ON (subscriptions.id) subscriptions.id,
subscription_carts.authorized_at as version_cart_authorized_at, subscriptions.*")
.joins("INNER JOIN subscription_carts on subscription_carts.subscription_id = subscriptions.id
AND subscription_carts.plan_id = subscriptions.plan_id")
.where("subscription_carts.status = 'processed'
AND subscriptions.status IN ('authorized','in_trial', 'paused')")
.order("subscriptions.id, subscription_carts.authorized_at")
}
uj5u.com熱心網友回復：
class Subscription < ApplicationRecord
def self.with_latest_subscription_cart
s = arel_table
sc = SubscriptionCart.arel_table.alias('sc')
select(
'DISTINCT ON(subscriptions.id)',
:id, # not sure why you want to select this explicitly
s[Arel.star],
sc[:authorized_at]
)
.joins(
s.join(sc).on(
sc[:subscription_id].eq(s[:id]).and(
sc[:plan_id].eq(s[:plan_id])
)
).join_sources
)
.where(
status: 'processed',
sc: {
status: ['authorized','in_trial', 'paused']
}
)
.order(:id, sc[:authorized_at])
end
end
但是，如果您改用subscription_carts.plan_id = subscriptions.plan_id該WHERE子句以便您可以只使用joins(:subscription_carts).
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/366139.html
標籤：红宝石轨道 PostgreSQL的 rails-activerecord
上一篇：RubyonRails連接鍵和值