Rieles: orden con nulos por última vez

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 

http://www.ruby-doc.org/core/classes/Array.html#M000271

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.

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.

fuente, comparación de la mayoría de los DBMS

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.

fuente: documentación de PostgreSQL

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.

fuente: documentación de MySQL

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.

fuente

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 .

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 
 Photo.order('collection_id DESC NULLS LAST') 

Sé que esta es una antigua, pero acabo de encontrar este fragmento y me funciona.

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

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 ( uno , dos , tres ). 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.

La forma más fácil es usar:

.order('name nulls first')

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

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.

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, ""])