PostgreSQL - должен появиться в предложении GROUP BY или использоваться в агрегатной функции

Я получаю эту ошибку в режиме производства pg, но она отлично работает в режиме разработки sqlite3.

 ActiveRecord::StatementInvalid in ManagementController#index

PG::Error: ERROR:  column "estates.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "estates".* FROM "estates"  WHERE "estates"."Mgmt" = ...
               ^
: SELECT "estates".* FROM "estates"  WHERE "estates"."Mgmt" = 'Mazzey' GROUP BY user_id

@myestate = Estate.where(:Mgmt => current_user.Company).group(:user_id).all

Ответы

Ответ 1

@myestate1 = Estate.where(:Mgmt => current_user.Company)
@myestate = @myestate1.select("DISTINCT(user_id)")

это то, что я сделал.

Ответ 2

Если user_id является PRIMARY KEY, вам необходимо обновить PostgreSQL; более новые версии будут правильно обрабатывать группировку с помощью первичного ключа.

Если user_id не является уникальным и основным ключом для рассматриваемого отношения "estates", то этот запрос не имеет большого смысла, поскольку PostgreSQL не имеет никакого способа узнать, какое значение для return для каждого столбца estates, где несколько строк имеют один и тот же user_id. Вы должны использовать агрегатную функцию, которая выражает то, что вы хотите, например min, max, avg, string_agg, array_agg и т.д., Или добавить интересующие столбцы к GROUP BY.

В качестве альтернативы вы можете перефразировать запрос, чтобы использовать DISTINCT ON и ORDER BY, если вы действительно хотите выбрать несколько произвольную строку, хотя я действительно сомневаюсь, что это можно выразить через ActiveRecord.

Некоторые базы данных, включая SQLite и MySQL, просто выбирают произвольную строку. Команда PostgreSQL считается неправильной и небезопасной, поэтому PostgreSQL следует стандарту SQL и считает, что такие запросы являются ошибками.

Если у вас есть:

col1    col2
fred    42
bob     9
fred    44
fred    99

и вы выполните:

SELECT col1, col2 FROM mytable GROUP BY col1;

тогда очевидно, что вы должны получить строку:

bob     9

но как насчет результата для fred? Не существует единого правильного ответа на выбор, поэтому база данных откажется выполнять такие небезопасные запросы. Если вы хотите наибольший col2 для любого col1, вы должны использовать агрегат max:

SELECT col1, max(col2) AS max_col2 FROM mytable GROUP BY col1;

Ответ 3

Недавно я перешел из MySQL в PostgreSQL и столкнулся с той же проблемой. Для справки, лучший подход, который я нашел, - использовать DISTINCT ON, как предлагается в этом SO-ответе:

Элегантная группа PostgreSQL для Ruby on Rails/ActiveRecord

Это позволит вам получить одну запись для каждого уникального значения в выбранном столбце, который соответствует другим условиям запроса:

MyModel.where(:some_col => value).select("DISTINCT ON (unique_col) *")

Я предпочитаю DISTINCT ON, потому что я все еще могу получить все остальные значения столбца в строке. Только DISTINCT вернет значение этого столбца.

Ответ 4

После получения самой ошибки я понял, что Rails (я использую rails 4) автоматически добавляет "order by id" в конце вашего запроса группировки. Это часто приводит к ошибке выше. Поэтому убедитесь, что вы добавили свой собственный .order(: group_by_column) в конце вашего запроса Rails. Следовательно, у вас будет что-то вроде этого:

@problems = Problem.select('problems.username, sum(problems.weight) as weight_sum').group('problems.username').order('problems.username')