Question 196119 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Result of query is `Model::ActiveRecord_Relation`. How can I call ActiveRecord methods (eg. `.where`) on it?

I’m struggling to understand why I’m unable to call .where on the results of what looks to be an ActiveRecord query.

I’m dipping down into SQL a bit to create virtual columns for matching. We have two models: Organization and Subscription. My query to fetch orgs with subscriptions is as follows:

def orgs_with_plans
  subquery = Subscription.select("DISTINCT ON (organization_id) *")
    .order(:organization_id, created_at: :desc)

  Organization
    .joins("LEFT JOIN (#{subquery.to_sql}) AS subscription_data ON organizations.id = subscription_data.organization_id")
    .select("
      organizations.*,
      (CASE
        WHEN subscription_data.id IS NULL THEN
          (CASE
            WHEN organizations.trial_end_date > CURRENT_DATE THEN 'trial'
            ELSE 'expired'
          END)
        ELSE subscription_data.plan_name
      END) AS plan_name
    ")
end

I can successfully get plan_name when I call the following:

orgs_with_plans.each do |org|
  puts "orgname: #{org.name}, plan name: #{org.plan_name}"
end

Additionally, the output sql of orgs_with_plans.to_sql runs as expected in my DB.

If I try to call orgs_with_plans.where('plan_name = ?', 'trial'), I run into an error:

ActiveRecord::StatementInvalid:
  PG::SyntaxError: ERROR:  syntax error at or near "AS"
  LINE 10:         END) AS plan_name

Why is this invalid syntax when I use count but totally fine otherwise?

Tags:

2

Answers


  1. 0

    The reason you are unable to directly call ActiveRecord methods like .count on the orgs_with_plans query result is because orgs_with_plans is not an array of records but an instance of ActiveRecord_Relation. However, you can still apply ActiveRecord methods to it by converting it into an array or by chaining the desired method at the end of the query.

    To obtain the count of organizations with a specific plan name, you can modify your query as follows:

      orgs_with_plans.where(plan_name: 'trial').count

    This will add a condition to the query to filter organizations with a plan name of ‘trial’ and then return the count.

    Regarding the error you encountered when using .where(‘plan_name = ?’, ‘trial’), the issue is with the syntax of the SQL statement that gets generated. The subquery you are using in the LEFT JOIN clause has an aliased column named plan_name, which is causing the error. To resolve this, you can modify your query to use the column index instead of the alias in the WHERE clause:

      orgs_with_plans.where('CASE WHEN subscription_data.id IS NULL THEN (CASE WHEN organizations.trial_end_date > CURRENT_DATE THEN ? ELSE ? END) ELSE subscription_data.plan_name END = ?', 'trial', 'expired', 'trial')

    In this modified query, the CASE statement is repeated in the WHERE clause, and the placeholders ? are used to pass the values ‘trial’, ‘expired’, and ‘trial’ respectively.

    Remember to adjust the placeholders and values according to your specific use case.

    By making these modifications, you should be able to use the .count method and apply additional conditions to your orgs_with_plans query result.

    Login or Signup to reply.
  2. 0

    When you are referring to plan_name inside the where, Rails is looking for a column named plan_name, which is not there. If you want to use the alias plan_name in the where condition, you gonna need CTE.

    You have to update your code a bit to make orgs_with_plans.where('plan_name = ?', 'trial') work:

    def orgs_with_trial_plans
  orgs_with_trial_plans = <<~SQL
    WITH orgs_with_plans AS
    (
      #{ActiveRecord::Base.sanitize_sql(orgs_with_plans.to_sql)}
    )
    SELECT name, plan_name
    FROM orgs_with_plans
    WHERE plan_name = 'trial'
  SQL

  ActiveRecord::Base.connection.execute(orgs_with_trial_plans).to_a
end

    Or if you don’t want to use SQL, you could use select to filter out plan_names with trial

    orgs_with_trial.select { |org| org.plan_name == 'trial' }

    Hope this helps!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search