Why does Postgres not accept my count column?

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.)

Solution

Thanks to all the comments and answers I have finally found a solution which I think is the best way to solve this.

First of, the issue occurred when I called pagy which tried to count my scope by appending .count(:all). This is what caused the errors. The solution was to not create a "field" in select() and use it in .order().

So here is the proper code:

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

This sorts the number of people on the number of unfulfilled votes for them, with the ability to count only votes for a given show, and it works with pagy(), and pagy_arel() which in my case is a much better fit, so the results can be properly paginated.