CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresql
pinkfloyd90
pinkfloyd90

Reputation: 678

PostgreSQL ordering by another attribute by default and then by whatever I define?

I've got an IndexQuery where I enrich a set of Users and then I apply different filters to it.

class Users::IndexQuery
  def initialize(users)
    @users = users
  end

  def call
    @users.kept.created_desc
      .select(
        "users.*",
        "COALESCE(paid_invoices.paid_amount_cents,0) AS paid_amount_cents",
        "COALESCE(unpaid_invoices.unpaid_amount_cents,0) AS unpaid_amount_cents",
      )
      .joins(joins_paid_invoices, joins_unpaid_invoices, :subscription)
      .includes(:subscription)
  end

  private

  def joins_paid_invoices
    @joins_paid_invoices ||= <<-SQL
      LEFT OUTER JOIN (
        SELECT invoices.user_id as user_id,
               SUM(invoices.amount_cents) as paid_amount_cents
        FROM invoices
        WHERE invoices.status = 'paid'
        GROUP BY user_id
      )
      AS paid_invoices
      ON paid_invoices.user_id = users.id
    SQL
  end

  def joins_unpaid_invoices
    @joins_unpaid_invoices ||= <<-SQL
      LEFT OUTER JOIN (
        SELECT invoices.user_id AS user_id,
               SUM(invoices.amount_cents) as unpaid_amount_cents
        FROM invoices
        WHERE invoices.status = 'unpaid'
        GROUP BY user_id
      )
      AS unpaid_invoices
      ON unpaid_invoices.user_id = users.id
    SQL
  end
end

This is what happens in the controller:

enriched_users = ::Users::IndexQuery.new(User.all.includes(:account)).call
if params.dig(:q, :extra_params, :preset_filter).present?
  enriched_users = enriched_users.where("subscriptions.status = ?", "in_trial").order("subscriptions.trial_end ASC")
end

However, running the query in the console with a .to_sql at the end prints the following:

ORDER BY \"users\".\"id\" DESC, subscriptions.trial_end ASC"

Nowhere in the controller nor the IndexQuery I specify that it should order by Users.id. What could be wrong and why is it ordering by that attribute by default?

EDIT: I do have a GROUP BY users.id in the IndexQuery. Could that be the problem?

Upvotes: 0

Views: 41

Answers (1)

max
max

Reputation: 102203

On Postgres 9.4+ you can simplefy this greatly by using FILTER which lets you put conditions on the result of aggregate functions:

# see https://github.com/rubocop/ruby-style-guide#namespace-definition
module  Users
  class IndexQuery
    def initialize(users)
      @users = users
    end

    def call
      @users.kept.created_desc
        .joins(:invoices)
        .group(:id)
        .select(
          User.arel_table[:*],
          'SUM(invoices.amount_cents) FILTER (WHERE invoices.paid = true) AS paid_amount_cents',
          'SUM(invoices.amount_cents) FILTER (WHERE invoices.paid = false) AS unpaid_amount_cents'
        )
    end

    # conventiance method so that you don't need to call
    # Users::IndexQuery.new(users).call
    def self.call(users)
       new(users).call
    end
  end
end

But honestly I don't see why you would extract this into its own object instead of just creating a scope / class method in the model as its a very overcomplicated solution.

Upvotes: 1

Related Questions