ser - PostgreSQL: debe aparecer en la cláusula GROUP BY o debe usarse en una función agregada

funcion group by sql

Question

Estoy obteniendo este error en el modo de producción de pg, pero funciona bien en el modo de desarrollo 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

Answer 1

Después de recibir el error a menudo, me di cuenta de que Rails (estoy usando rails 4) agrega automáticamente un ''orden por id'' al final de su consulta de agrupación. Esto a menudo resulta en el error anterior. Así que asegúrese de agregar su propio .order (: group_by_column) al final de su consulta de Rails. Por lo tanto, tendrá algo como esto:

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

Answer 2

Recientemente me mudé de MySQL a PostgreSQL y encontré el mismo problema. Solo como referencia, el mejor enfoque que he encontrado es usar DISTINCT ON como se sugiere en esta respuesta SO:

Elegante grupo PostgreSQL por Ruby on Rails / ActiveRecord

Esto le permitirá obtener un registro por cada valor único en la columna elegida que coincida con las otras condiciones de consulta:

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

Prefiero DISTINCT ON porque todavía puedo obtener todos los otros valores de columna en la fila. DISTINCT solo solo devolverá el valor de esa columna específica.

Answer 3

Si user_id es la PRIMARY KEY, entonces necesita actualizar PostgreSQL; las versiones más nuevas manejarán correctamente la agrupación por la clave principal.

Si user_id no es único ni la clave primaria para la relación ''estates'' en cuestión, entonces esta consulta no tiene mucho sentido, ya que PostgreSQL no tiene manera de saber qué valor devolver para cada columna de estates donde varias filas comparten el mismo user_id . Debe usar una función agregada que exprese lo que desea, como min , max , avg , string_agg , array_agg , etc. o agregue la (s) columna (s) de interés para GROUP BY .

Alternativamente puede reformular la consulta para usar DISTINCT ON y un ORDER BY si realmente desea elegir una fila algo arbitraria, aunque realmente dudo que sea posible expresar eso a través de ActiveRecord.

Algunas bases de datos, incluyendo SQLite y MySQL, elegirán una fila arbitraria. El equipo de PostgreSQL lo considera incorrecto e inseguro, por lo que PostgreSQL sigue el estándar SQL y considera que dichas consultas son errores.

Si usted tiene:

col1 col2
fred 42
bob 9
fred 44
fred 99

y lo hace:

SELECT col1, col2 FROM mytable GROUP BY col1;

entonces es obvio que deberías obtener la fila:

bob 9

pero ¿y el resultado para fred ? No hay una sola respuesta correcta para elegir, por lo que la base de datos se negará a ejecutar tales consultas inseguras. Si quería la col2 más col2 para cualquier col1 , usaría el max agregado:

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

Answer 4

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

Esto es lo que hice.