Wstęp
Mamy przykładową tabelę z userami:
1 2 3 4 5 6 7 8 9 10 11 | DROP TABLE users; CREATE TABLE users( id SERIAL PRIMARY KEY , name VARCHAR (50), created TIMESTAMP DEFAULT NOW() ); INSERT INTO users( name ) VALUES ( 'Marcin' ); INSERT INTO users( name ) VALUES ( 'Bartek' ); INSERT INTO users( name ) VALUES ( 'Agnieszka' ); INSERT INTO users( name ) VALUES ( 'Dominika' ); |
Na tabeli wykonywane były wielokrotnie operacje INSERT/UPDATE/DELETE przykładowo:
1 2 3 | UPDATE users SET name = 'Ania' WHERE name = 'Bartek' ; UPDATE users SET name = 'Dalia' WHERE name = 'Marcin' ; DELETE FROM users WHERE name = 'Ania' ; |
Zatem SELECT bez orderu wyświetli nam dane posortowane względem kolejności modyfikacji:
1 2 3 4 5 6 7 8 9 | SELECT * FROM users; psql=> select * from users; id | name | created ----+-----------+---------------------------- 3 | Agnieszka | 2009-12-19 10:41:49.324851 4 | Dominika | 2009-12-19 10:41:49.958792 1 | Dalia | 2009-12-19 10:41:49.297223 (3 rows ) |
W interface aplikacji chcemy oprogramować własną obsługę kolejności rekordów. Dodajemy zatem kolumnę, która będzie obsługiwać tą funkcjonalność.
1 | ALTER TABLE users ADD COLUMN order_id INTEGER ; |
Problem
Musimy teraz uzupełnić kolumne order_id o poprawne wartości. Kolejność orderu danych, które znajdują się w bazie powinna być kolejności wprowadzania tych danych do tabeli (czyli zgodna z kolejnością pola id).
Aby zrealizować poprawne uzupełnienie orderu tworzymy pomocniczą sekwencję, która wykorzystamy do wygenerowania kolejnych liczb do orderu:
1 2 | DROP SEQUENCE order_id_seq; CREATE SEQUENCE order_id_seq; |
Wykonujemy oczywiste zapytanie przypisujące rekordom z tabeli users kolejne wartości sekwencji:
1 | UPDATE users SET order_id = NEXTVAL( 'order_id_seq' ); |
Niestety przez to, że dane nie były ustawione w dobrej kolejności ORDER ustawiony jest niepoprawnie.
1 2 3 4 5 6 7 | psql=> SELECT * FROM users; id | name | created | order_id ----+-----------+----------------------------+---------- 3 | Agnieszka | 2009-12-19 10:41:49.324851 | 1 4 | Dominika | 2009-12-19 10:41:49.958792 | 2 1 | Dalia | 2009-12-19 10:41:49.297223 | 4 (3 rows ) |
Rozwiązanie
Aby UPDATE poprawnie ustawił kolejność orderu należy nieco zmodyfikować zapytanie i nadać danym z tabeli users odpowiednią kolejność.
W tym celu tworzymy podzapytanie sorted_ids, w którym poprzez ORDER BY ustalamy kolejność rekordów, które następnie w zapytaniu nadrzędnym zostaną zmodyfikowane właśnie w tej kolejności.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | UPDATE users SET order_id = correct_order_id FROM ( SELECT id, nextval( 'order_id_seq' ) as correct_order_id FROM users ORDER BY id ASC ) as sorted_ids WHERE users.id = sorted_ids.id; select * from users order by order_id; |
Jak widać dane zostały pięknie zorderowane:
1 2 3 4 5 6 7 | psql=> select * from users order by order_id; id | name | created | order_id ----+-----------+----------------------------+---------- 1 | Dalia | 2009-12-19 10:41:49.297223 | 1 3 | Agnieszka | 2009-12-19 10:41:49.324851 | 2 4 | Dominika | 2009-12-19 10:41:49.958792 | 3 (3 rows ) |
Przy okazji wspomnę, że w aplikacji łatwiej obsłużyć ordering odwrotny, tzn. wiersze, które posiadają największą wartość order_id są na czele a nie na końcu listy. Przy takim podejściu przy wstawianiu nowego rekordu pole order_id powinno mieć wartość MAX(order_id) + 1. Dla orderingu odwrotnego należy zmienić w podzapytaniu kolejność sortowania z id ASC na id DESC.
Należy także dbać o to, aby przy usunięciu rekordu prenumerować order_id wszystkich rekordów, które znajdują się 'nad nim’ o -1.