I am building a Rails app with the following models:

# vote.rb
class Vote < ApplicationRecord
  belongs_to :person
  belongs_to :show
  scope :fulfilled, -> { where(fulfilled: true) }
  scope :unfulfilled, -> { where(fulfilled: false) }
end

# person.rb
class Person < ApplicationRecord
  has_many :votes, dependent: :destroy

  def self.order_by_votes(show = nil)
    count = 'nullif(votes.fulfilled, true)'
    count = "case when votes.show_id = #{show.id} AND NOT votes.fulfilled then 1 else null end" if show
    people = left_joins(:votes).group(:id).uniq!(:group)
    people = people.select("people.*, COUNT(#{count}) AS people.vote_count")
    people.order('people.vote_count DESC')
  end
end

The idea behind order_by_votes is to sort People by the number of unfulfilled votes, either counting all votes, or counting only votes associated with a given Show.

This seem to work fine when I test against SQLite. But when I switch to Postgres I get this error:

Error:
PeopleControllerIndexTest#test_should_get_previously_on_show:
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column people.vote_count does not exist
LINE 1: ...s"."show_id" = $1 GROUP BY "people"."id" ORDER BY people.vot...
                                                             ^

If I dump the SQL using @people.to_sql, this is what I get:

SELECT people.*, COUNT(nullif(votes.fulfilled, true)) AS people.vote_count FROM "people" LEFT OUTER JOIN "votes" ON "votes"."person_id" = "people"."id" GROUP BY "people"."id" ORDER BY people.vote_count DESC

Why is this failing on Postgres but working on SQLite? And what should I be doing instead to make it work on Postgres?

(PS: I named the field people.vote_count, with a dot, so I can access it in my view without having to do another SQL query to actually view the vote count for each person in the view (not sure if this works) but I get the same error even if I name the field simply vote_count.)

(PS2: I recently added the .uniq!(:group) because of some deprecation warning for Rails 6.2, but I couldn't find any documentation for it so I am not sure I am doing it right, still the error is there without that part.)

CodePudding user response：

Are you sure you're not getting a syntax error from PostgreSQL somewhere? If you do something like this:

select count(*) as t.vote_count from t ... order by t.vote_count

I get a syntax error before PostgreSQL gets to complain about there being no t.vote_count column.

No matter, the solution is to not try to put your vote_count in the people table:

people = people.select("people.*, COUNT(#{count}) AS vote_count")
...
people.order(vote_count: :desc)

You don't need it there, you'll still be able to reference the vote_count just like any "normal" column in people. Anything in the select list will appear as an accessor in the resultant model instances whether they're columns or not, they won't show up in the #inspect output (since that's generated based on the table's columns) but you call the accessor methods nonetheless.

CodePudding user response：

Historically there have been quite a few AR problems (and bugs) in getting the right count by just using count on a scope, and I am not sure they are actually all gone.

That depends on the scope (AR version, relations, group, sort, uniq, etc). A defaut count call that a gem has to generically use on a scope is not a one-fit-all solution. For that known reason Pagy allows you to pass the right count to its pagy method as explained in the Pagy documentation.

Your scope might become complex and the default pagy collection.count(:all) may not get the actual count. In that case you can get the right count with some custom statement, and pass it to pagy.

@pagy, @records = pagy(collection, count: your_count)

Notice: pagy will efficiently skip its internal count query and will just use the passed :count variable.

So... just get your own calculated count and pass it to pagy, and it will not even try to use the default.

EDIT: I forgot to mention: you may want to try the pagy arel extra that:

adds specialized pagination for collections from sql databases with GROUP BY clauses, by computing the total number of results with COUNT(*) OVER ().