Postgres query works on console but using it as a scope in a view renders syntax errors-CodePudding

I'm using a query to fetch the SubscriptionCart with the oldest authorized_at date from a Subscription.

Since a Subscription has_many SubscriptionCart, I filter the carts with the same plan_id as the Subscription plan_id. I also use WHERE statements for the Subscription and SubscriptionCart statuses as well.

The query seems to work fine on console, but when I put it in a Subscription scope and try to use it in my views I get:

PG::SyntaxError: ERROR:  syntax error at or near "WHERE"
LINE 6: ...ubscriptions.id, subscription_carts.authorized_at WHERE "sub...

What is going on?

query:

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:

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

EDIT: Moving the WHERE, AND & ORDER BY clauses outside of the .join fixed the issue.

updated scope looks like:

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

CodePudding user response：

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

It would be a lot easier though if you moved subscription_carts.plan_id = subscriptions.plan_id into the WHERE clause instead so that you can just use joins(:subscription_carts).