ruby-on-rails - joins - rails sql query
Rieles: orden con nulos por última vez (11)
A pesar de que es 2017 ahora, todavía hay un consenso sobre si NULL debe tener prioridad. Sin que sea explícito al respecto, los resultados variarán según el DBMS.
El estándar no especifica cómo deben ordenarse los NULL en comparación con los valores que no son NULL, excepto que cualquiera de los dos NULL debe considerarse igual de ordenado, y que los NULL deben ordenar por encima o por debajo de todos los valores no NULL.
Para ilustrar el problema, compilé una lista de algunos casos más populares cuando se trata del desarrollo de Rails:
PostgreSQL
NULL s tienen el valor más alto.
Por defecto, los valores nulos se ordenan como si fueran más grandes que cualquier valor no nulo.
MySQL
NULL s tiene el valor más bajo.
Al hacer un ORDER BY, los valores NULL se presentan primero si lo hace ORDER BY ... ASC y el último si lo hace ORDER BY ... DESC.
SQLite
NULL s tiene el valor más bajo.
Una fila con un valor NULO es más alta que las filas con valores regulares en orden ascendente, y se invierte para orden descendente.
Solución
Desafortunadamente, Rails en sí mismo todavía no proporciona una solución.
PostgreSQL específico
Para PostgreSQL puedes usar muy intuitivamente:
Photo.order(''collection_id DESC NULLS LAST'') # NULLs come last
MySQL específico
Para MySQL, puede poner el signo menos por adelantado, pero esta característica parece no documentada. Parece que funciona no solo con valores numéricos, sino también con fechas.
Photo.order(''-collection_id DESC'') # NULLs come last
PostgreSQL y MySQL específicos
Para cubrir ambos, esto parece funcionar:
Photo.order(''collection_id IS NULL, collection_id DESC'') # NULLs come last
Aún así, este no funciona en SQLite.
Solución universal
Para proporcionar soporte cruzado para todos los DBMS, tendría que escribir una consulta usando CASE , ya sugerida por @PhilIT:
Photo.order(''CASE WHEN collection_id IS NULL THEN 1 ELSE 0 END, collection_id'')
lo que se traduce en la primera clasificación de cada uno de los registros primero por los resultados CASE (por orden ascendente por defecto, lo que significa que los valores NULL serán los últimos), en segundo lugar por calculation_id .
En la aplicación Rails me he encontrado con un problema un par de veces que me gustaría saber cómo resuelven otras personas:
Tengo ciertos registros donde un valor es opcional, por lo que algunos registros tienen un valor y algunos son nulos para esa columna.
Si ordeno por esa columna en algunas bases de datos, los nulos se ordenan primero y en algunas bases de datos los nulos se ordenan al final.
Por ejemplo, tengo fotos que pueden pertenecer o no a una colección, es decir, hay algunas fotos donde collection_id=nil y algunas donde collection_id=1 etc.
Si hago Photo.order(''collection_id desc) entonces en SQLite obtengo los nulls pero en PostgreSQL obtengo los nulos primero.
¿Existe una buena forma estándar de Rails para manejar esto y obtener un rendimiento consistente en cualquier base de datos?
Agregar matrices juntas preservará el orden:
@nonull = Photo.where("collection_id is not null").order("collection_id desc")
@yesnull = Photo.where("collection_id is null")
@wanted = @nonull+@yesnull
Coloque el signo menos delante de column_name e invierta la dirección de la orden. Funciona en mysql. Más detalles
Product.order(''something_date ASC'') # NULLS came first
Product.order(''-something_date DESC'') # NULLS came last
En mi caso, necesitaba líneas de ordenamiento por fecha de inicio y finalización por parte de ASC, pero en algunos casos end_date era nulo y las líneas deberían estar arriba, utilicé
@invoice.invoice_lines.order(''start_date ASC, end_date ASC NULLS FIRST'')
La forma más fácil es usar:
.order(''name nulls first'')
No soy un experto en SQL, pero ¿por qué no simplemente ordenar por si algo es nulo primero y luego ordenarlo por la forma en que desea ordenarlo.
Photo.order(''collection_id IS NULL, collection_id DESC'') # Null''s last
Photo.order(''collection_id IS NOT NULL, collection_id DESC'') # Null''s first
Si solo está usando PostgreSQL, también puede hacer esto
Photo.order(''collection_id DESC NULLS LAST'') #Null''s Last
Photo.order(''collection_id DESC NULLS FIRST'') #Null''s First
Pero SQLite3 le dará errores.
Parece que tendría que hacerlo en Ruby si desea resultados uniformes en todos los tipos de bases de datos, ya que la base de datos interpreta si los NULLS van al principio o al final de la lista.
Photo.all.sort {|a, b| a.collection_id.to_i <=> b.collection_id.to_i}
Pero eso no es muy eficiente.
Por posteridad, quería resaltar un error de ActiveRecord relacionado con NULLS FIRST .
Si intentas llamar:
Model.scope_with_nulls_first.last
Rails intentará llamar a reverse_order.first , y reverse_order no es compatible con NULLS LAST , ya que intenta generar el SQL no válido:
PG::SyntaxError: ERROR: syntax error at or near "DESC"
LINE 1: ...dents" ORDER BY table_column DESC NULLS LAST DESC LIMIT...
Esto fue referenciado hace algunos años en algunos problemas de Rails aún abiertos ( one , two , three ). Pude solucionarlo haciendo lo siguiente:
scope :nulls_first, -> { order("table_column IS NOT NULL") }
scope :meaningfully_ordered, -> { nulls_first.order("table_column ASC") }
Parece que al encadenar los dos pedidos juntos, se genera un SQL válido:
Model Load (12.0ms) SELECT "models".* FROM "models" ORDER BY table_column IS NULL DESC, table_column ASC LIMIT 1
El único inconveniente es que este encadenamiento tiene que hacerse para cada ámbito.
Primero obtén fotos donde collection_id no es nulo y ordena por collection_id descendente.
A continuación, obtenga fotos donde collection_id sea nulo y añádalas.
Photos.where.not(collection_id: [nil, ""])
.order(collection_id: :desc) + where(collection_id: [nil, ""])
Un poco tarde para el show, pero hay una manera genérica de SQL para hacerlo. Como de costumbre, CASE al rescate.
Photo.order(''CASE WHEN collection_id IS NULL THEN 1 ELSE 0 END, collection_id'')
Photo.order(''collection_id DESC NULLS LAST'')
Sé que esta es una antigua, pero acabo de encontrar este fragmento y me funciona.