Rails: ultimo ordine con null

Nella mia app Rails ho riscontrato un problema un paio di volte che mi piacerebbe sapere come le altre persone risolvono:

Ho determinati record in cui un valore è facoltativo, quindi alcuni record hanno un valore e alcuni sono nulli per quella colonna.

Se ordino da quella colonna su alcuni database, i null vengono ordinati per primi e su alcuni database l’ordinamento dei null dura per ultimo.

Ad esempio, ho delle foto che possono o non possono appartenere a una collezione, cioè ci sono alcune foto dove collection_id=nil e alcune dove collection_id=1 ecc.

Se faccio Photo.order('collection_id desc) poi su SQLite ottengo i null ultimi ma su PostgreSQL ottengo i null prima.

C’è un modo piacevole e standard di Rails per gestire questo e ottenere prestazioni costanti su qualsiasi database?

L’aggiunta di array consente di preservare l’ordine:

 @nonull = Photo.where("collection_id is not null").order("collection_id desc") @yesnull = Photo.where("collection_id is null") @wanted = @[email protected] 

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

Non sono esperto di SQL, ma perché non basta ordinare se qualcosa è nullo, quindi ordinare in base a come si desidera ordinarlo.

 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 

Se stai usando solo PostgreSQL, puoi farlo anche tu

 Photo.order('collection_id DESC NULLS LAST') #Null's Last Photo.order('collection_id DESC NULLS FIRST') #Null's First 

Ma SQLite3 ti darà errori.

Anche se è ora il 2017, c’è ancora un consenso sul fatto che i NULL debbano avere la precedenza. Senza esserne espliciti, i risultati varieranno a seconda del DBMS.

Lo standard non specifica come ordinare NULL in confronto con valori non NULL, eccetto che due NULL devono essere considerati ugualmente ordinati e che i NULL devono ordinare sopra o sotto tutti i valori non NULL.

fonte, confronto della maggior parte dei DBMS

Per illustrare il problema, ho compilato un elenco di alcuni casi più popolari quando si tratta dello sviluppo di Rails:

PostgreSQL

NULL hanno il valore più alto.

Per impostazione predefinita, i valori nulli vengono ordinati come se fossero più grandi di qualsiasi valore non nullo.

fonte: documentazione PostgreSQL

MySQL

NULL hanno il valore più basso.

Quando si esegue un ordine BY, i valori NULL vengono presentati per primi se si esegue ORDER BY … ASC e l’ultimo se si esegue ORDER BY … DESC.

fonte: documentazione MySQL

SQLite

NULL hanno il valore più basso.

Una riga con un valore NULL è più alta delle righe con valori normali in ordine crescente e viene invertita per ordine decrescente.

fonte

Soluzione

Sfortunatamente, Rails stesso non fornisce ancora una soluzione.

PostgreSQL specifico

Per PostgreSQL puoi usare in modo abbastanza intuitivo:

Photo.order('collection_id DESC NULLS LAST') # NULLs come last

MySQL specifico

Per MySQL, è ansible inserire il segno meno in anticipo, tuttavia questa funzione sembra non documentata. Sembra funzionare non solo con valori numerici, ma anche con date.

Photo.order('-collection_id DESC') # NULLs come last

PostgreSQL e MySQL specifici

Per coprire entrambi, sembra funzionare:

Photo.order('collection_id IS NULL, collection_id DESC') # NULLs come last

Tuttavia, questo non funziona in SQLite.

Soluzione universale

Per fornire supporto incrociato per tutti i DBMS, devi scrivere una query utilizzando CASE , già suggerito da @PhilIT:

Photo.order('CASE WHEN collection_id IS NULL THEN 1 ELSE 0 END, collection_id')

che si traduce per prima cosa nell’ordinare ciascuno dei record in base ai risultati CASE (per impostazione predefinita l’ordine crescente, che significa che i valori NULL saranno gli ultimi), in secondo luogo con calculation_id .

Metti il segno meno davanti a column_name e inverti la direzione dell’ordine. Funziona su mysql. Più dettagli

 Product.order('something_date ASC') # NULLS came first Product.order('-something_date DESC') # NULLS came last 
 Photo.order('collection_id DESC NULLS LAST') 

So che questo è vecchio ma ho appena trovato questo frammento e funziona per me.

Un po ‘tardi per lo spettacolo, ma c’è un modo SQL generico per farlo. Come al solito, CASE in soccorso.

 Photo.order('CASE WHEN collection_id IS NULL THEN 1 ELSE 0 END, collection_id') 

Per i posteri, volevo evidenziare un errore ActiveRecord relativo a NULLS FIRST .

Se provi a chiamare:

 Model.scope_with_nulls_first.last 

Rails tenterà di chiamare reverse_order.first e reverse_order non è compatibile con NULLS LAST , poiché tenta di generare l’SQL non valido:

 PG::SyntaxError: ERROR: syntax error at or near "DESC" LINE 1: ...dents" ORDER BY table_column DESC NULLS LAST DESC LIMIT... 

Questo è stato fatto riferimento alcuni anni fa in alcuni problemi Rails ancora aperti ( uno , due , tre ). Sono stato in grado di aggirarlo facendo quanto segue:

  scope :nulls_first, -> { order("table_column IS NOT NULL") } scope :meaningfully_ordered, -> { nulls_first.order("table_column ASC") } 

Sembra che concatenando i due ordini insieme, venga generato un SQL valido:

 Model Load (12.0ms) SELECT "models".* FROM "models" ORDER BY table_column IS NULL DESC, table_column ASC LIMIT 1 

L’unico svantaggio è che questo concatenamento deve essere fatto per ogni ambito.

Il modo più semplice è usare:

.order('name nulls first')

Nel mio caso avevo bisogno di ordinare le righe per data di inizio e fine con ASC, ma in alcuni casi end_date era nullo e le righe dovevano essere in alto, io usavo

@invoice.invoice_lines.order('start_date ASC, end_date ASC NULLS FIRST')

Sembra che dovresti farlo in Ruby se vuoi risultati coerenti tra i tipi di database, dato che il database stesso interpreta se i NULLS vanno avanti o alla fine della lista.

 Photo.all.sort {|a, b| a.collection_id.to_i <=> b.collection_id.to_i} 

Ma non è molto efficiente.

Per prima cosa ottieni le foto dove collection_id non è nullo e ordina decrescente con collection_id .

Quindi ottieni le foto dove collection_id è nullo e aggiungili.

 Photos.where.not(collection_id: [nil, ""]) .order(collection_id: :desc) + where(collection_id: [nil, ""])