W mojej aplikacji Railsowej kilka razy napotkałem problem, który chciałbym wiedzieć, jak rozwiązują go inni ludzie:

Mam pewne rekordy, w których wartość jest opcjonalna, więc niektóre rekordy mają wartość, a niektóre są puste dla tej kolumny.

Jeśli uporządkuję według tej kolumny w niektórych bazach danych, wartości null są sortowane jako pierwsze, a w niektórych bazach danych wartości null są sortowane jako ostatnie.

Na przykład mam zdjęcia, które mogą, ale nie muszą należeć do kolekcji, tj. Są tam zdjęcia, gdzie collection_id=nili gdzie collection_id=1itp.

Jeśli to zrobię, Photo.order('collection_id desc)w SQLite otrzymuję wartości null jako ostatnie, ale w PostgreSQL najpierw otrzymuję wartości null.

Czy istnieje ładny, standardowy sposób Railsów na to, aby poradzić sobie z tym i uzyskać stałą wydajność w dowolnej bazie danych?

Dodawanie tablic razem zachowa porządek:

@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

Nie jestem ekspertem w SQL, ale dlaczego nie posortować najpierw według tego, czy coś jest zerowe, a następnie posortować według tego, jak chcesz to posortować.

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

Jeśli używasz tylko PostgreSQL, możesz to również zrobić

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

Jeśli chcesz czegoś uniwersalnego (na przykład używasz tego samego zapytania w kilku bazach danych, możesz użyć (dzięki uprzejmości @philT)

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

Mimo że jest teraz 2017 r., Nadal nie ma konsensusu, czy NULLpowinny one mieć pierwszeństwo. Bez wyrażenia tego wprost, wyniki będą się różnić w zależności od DBMS.

Standard nie określa, jak należy uporządkować wartości NULL w porównaniu z wartościami innymi niż NULL, z wyjątkiem tego, że dowolne dwie wartości NULL należy traktować jako jednakowo uporządkowane, a wartości NULL powinny być sortowane powyżej lub poniżej wszystkich wartości innych niż NULL.

źródło, porównanie większości DBMS

Aby zilustrować problem, stworzyłem listę kilku najpopularniejszych przypadków, jeśli chodzi o rozwój Railsów:

PostgreSQL

NULLs mają najwyższą wartość.

Domyślnie wartości null są sortowane tak, jakby były większe niż jakakolwiek inna wartość niż null.

źródło: dokumentacja PostgreSQL

MySQL

NULLs mają najniższą wartość.

Podczas wykonywania ORDER BY, wartości NULL są prezentowane jako pierwsze, jeśli wykonujesz ORDER BY ... ASC, i ostatnie, jeśli wykonujesz ORDER BY ... DESC.

źródło: dokumentacja MySQL

SQLite

NULLs mają najniższą wartość.

Wiersz z wartością NULL jest wyższy niż wiersze ze zwykłymi wartościami w kolejności rosnącej i jest odwrócony w kolejności malejącej.

źródło

Rozwiązanie

Niestety, same Railsy nie zapewniają jeszcze rozwiązania tego problemu.

Specyficzne dla PostgreSQL

W przypadku PostgreSQL możesz całkiem intuicyjnie użyć:

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

Specyficzne dla MySQL

W przypadku MySQL można umieścić znak minus z góry, ale ta funkcja wydaje się nie być udokumentowana. Wydaje się, że działa nie tylko z wartościami liczbowymi, ale także z datami.

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

Specyficzne dla PostgreSQL i MySQL

Aby objąć oba z nich, wydaje się, że działa to:

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

Mimo to ten nie działa w SQLite.

Uniwersalne rozwiązanie

Aby zapewnić obsługę krzyżową dla wszystkich DBMS, musiałbyś napisać zapytanie przy użyciu CASE, co sugeruje @PhilIT:

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

co przekłada się na pierwsze sortowanie każdego z rekordów najpierw według CASEwyników (domyślnie rosnąco, co oznacza, że NULLwartości będą ostatnie), a następnie calculation_id.

Photo.order('collection_id DESC NULLS LAST')

Wiem, że to stary, ale właśnie znalazłem ten fragment i działa dla mnie.

Umieść znak minus przed nazwa_kolumny i odwróć kolejność. Działa na mysql. Więcej szczegółów

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

Trochę za późno, ale jest na to ogólny sposób SQL. Jak zwykle CASEna ratunek.

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

Najłatwiej jest użyć:

.order('name nulls first')

Ze względu na potomność chciałem podkreślić błąd ActiveRecord dotyczący NULLS FIRST .

Jeśli spróbujesz zadzwonić:

Model.scope_with_nulls_first.last

Railsy będą próbowały wywołać reverse_order.first, ale reverse_ordernie są zgodne z NULLS LAST, ponieważ próbują wygenerować nieprawidłowy SQL:

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

Odwołano się do tego kilka lat temu w niektórych wciąż otwartych problemach z Railsami ( jeden , dwa , trzy ). Udało mi się to obejść, wykonując następujące czynności:

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

Wygląda na to, że łącząc te dwa zamówienia w łańcuch, generowany jest prawidłowy kod SQL:

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

Jedynym minusem jest to, że to połączenie należy wykonać dla każdego zakresu.

W moim przypadku potrzebowałem sortować wiersze według daty rozpoczęcia i zakończenia według ASC, ale w kilku przypadkach data_końcowa była pusta i te wiersze powinny znajdować się powyżej, użyłem

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

Wygląda na to, że musisz to zrobić w Rubim, jeśli chcesz uzyskać spójne wyniki w różnych typach baz danych, ponieważ sama baza danych interpretuje, czy NULLS znajdują się na początku lub na końcu listy.

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

Ale to nie jest zbyt wydajne.