Почему Postgres не принимает мой столбец подсчета?

Question

Я создаю приложение Rails со следующими моделями:

      # 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

Идея, лежащая в основе order_by_votes это сортировать People по количеству невыполненных голосов, либо с учетом всех голосов, либо с учетом только голосов, связанных с данным Show.

Кажется, это нормально работает, когда я тестирую SQLite. Но когда я переключаюсь на Postgres, я получаю такую ​​ошибку:

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

Если я сбрасываю SQL, используя @people.to_sql, вот что я получаю:

ВЫБЕРИТЕ людей. *, COUNT (nullif(messages.fulfilled, true)) КАК people.vote_count ОТ "people" ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ "голосов" НА "голосов". "Person_id" = "people". "Id" GROUP BY "people"." id "ЗАКАЗАТЬ people.vote_count DESC

Почему это не удается на Postgres, но работает на SQLite? И что мне делать вместо этого, чтобы он работал на Postgres?

(PS: я назвал поле people.vote_count, с точкой, поэтому я могу получить к нему доступ в моем представлении, не выполняя еще один SQL-запрос, чтобы фактически просмотреть подсчет голосов для каждого человека в представлении (не уверен, работает ли это), но я получаю ту же ошибку, даже если назову поле просто vote_count.)

(PS2: недавно я добавил .uniq!(:group) из-за некоторого предупреждения об устаревании Rails 6.2, но я не смог найти для него никакой документации, поэтому я не уверен, что делаю это правильно, но ошибка остается без этой части.)

Answer 1

Благодаря всем комментариям и ответам я наконец нашел решение, которое, на мой взгляд, является лучшим способом решить эту проблему.

Во-первых, проблема возникла, когда я позвонил pagy который пытался подсчитать мой объем, добавив .count(:all). Вот что вызвало ошибки. Решением было не создавать «поле» в select() и использовать его в .order().

Итак, вот правильный код:

      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

Это сортирует количество людей по количеству невыполненных голосов за них, с возможностью подсчитывать только голоса за данное шоу, и это работает с pagy(), а также pagy_arel() что в моем случае намного лучше подходит, поэтому результаты можно правильно разбить на страницы.

Answer 2

Вы уверены, что где-то не получаете синтаксическую ошибку от PostgreSQL? Если вы сделаете что-то вроде этого:

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

Я получаю синтаксическую ошибку до того, как PostgreSQL начинает жаловаться на отсутствие t.vote_count столбец.

Как бы то ни было, выход в том, чтобы не пытаться поставить свою точку в таблице:

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

Он вам не нужен, вы все равно сможете ссылаться на vote_count как и любой "нормальный" столбец в people. Все, что есть в списке выбора, будет отображаться как средство доступа в результирующих экземплярах модели, независимо от того, столбцы они или нет, они не будут отображаться в #inspect output (поскольку он генерируется на основе столбцов таблицы), но вы, тем не менее, вызываете методы доступа.

Answer 3

Historically there have been quite a few AR problems (and bugs) in getting the right count by just using 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 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 ().