UPDATE z ORDER BY w jednym zapytaniu na przykładzie orderingu danych

Wstęp

Mamy przykładową tabelę z userami:

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:

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:

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ść.

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:

DROP SEQUENCE order_id_seq;
CREATE SEQUENCE order_id_seq;

Wykonujemy oczywiste zapytanie przypisujące rekordom z tabeli users kolejne wartości sekwencji:

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.

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.

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:

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.

Startup LubimyCzytać.pl – społeczność czytelników!

LubimyCzytać.pl - społeczność czytelników Przez ostatnie miesiące zaangażowany byłem w autorski projekt LubimyCzytać.pl.

Po wielu zmaganiach z przeciwnościami losu i własnym lenistwem serwis ruszył pod koniec listopada. Muszę przyznać, że czas powstawania serwisu był dosyć długi a tworzenie serwisów takiej wielkości 'po godzinach’ w dwie osoby bez dodatkowego finansowania i wsparcia jest dosyć męczące.

Nie chcę teraz przynudzać o moich przemyśleniach związanych z powstawaniem i tworzeniem startupa, jeżeli jesteście zainteresowani kulisami oraz wnioskami płynącymi z tego doświadczenia, to proszę o info w komentarzach. Jak będzie zainteresowanie, to przeznaczę na to oddzielny wpis.

Czym jest LubimyCzytać.pl?

Głównym celem serwisu LubimyCzytać.pl jest umożliwienie wymiany informacji pomiędzy ludźmi, którzy lubią czytać książki. Poprzez opinie, recenzje i oceny przeczytanych książek użytkownicy mogą sobie nawzajem polecać książki warte uwagi.

Główną funkcjonalnością serwisu jest „wirtualna biblioteczka” czyli miejsce, w którym każdy czytelnik może składować książki które przeczytał, teraz czyta oraz chce przeczytać. W biblioteczce można tworzyć własne półki i zapełniać je książkami.

Na podstawie książek zgromadzonych w biblioteczce oraz ocen książek system proponuje spersonalizowaną listę książek, które powinny zaciekawić czytelnika. Z uwagi na aktualnie panujący bałagan w bazie danych książek funkcjonalność nie spełnia swojego zadania, jednak wraz ze stopniowym oczyszczaniem zdublowanych pozycji funkcjonalność ta powinna zacząć spełniać swoje zadanie i przynieść użytkownikowi dużą korzyść.

Jak na serwis społecznościowy przystało nie mogło zabraknąć funkcjonalności obserwowania. W LubimyCzytać.pl można obserwować innych czytelników, dzięki temu będziemy na bieżąco z książkami i ocenami obserwowanej osoby.

Chciałbym, aby serwis LubimyCzytać.pl stał się w pewnym sensie internetowym centrum wymiany informacji o książkach. Swoistym kompendium w którym miejsce znaleźliby zarówno namiętni czytelnicy jak i osoby nie czytające a jedynie szukające ciekawych książek na prezent.

Zapowiedzi funkcjonalności

Zdaję sobie sprawę, że w aktualnej wersji w serwisie brakuje wielu przydatnych funkcjonalności. Opublikowana wersja jest wersją bazową, która skupia się na najważniejszej funkcjonalności bez której serwis nie miałby sensu czyli wirtualnej biblioteczce.

Będziemy sukcesywnie dodawać kolejne funkcjonalności, na razie zrealizowaliśmy funkcjonalność „wklejki na stronę/wigeta” a w kolejce czekają następne:

  • dodawanie i edycja książek i autorów przez użytkowników,
  • fora/grupy tematyczne dla książek i autorów,
  • system rang użytkowników,
  • cykliczne konkursy,
  • integracja z księgarniami internetowymi,
  • komentowanie i ocenianie recenzji,
  • mini bazar kupię/sprzedam/wymienię,
  • tablica informacyjna o nadchodzących książkowych wydarzeniach,
  • integracja kont użytkowników z microblogami (blip, flaker, spinacz),
  • udostępnienie API,

i wiele innych… Jesteśmy otwarci na sugestie i propozycje współpracy :-)

Zapraszam na LubimyCzytać.pl

Serdecznie zapraszam do korzystania z serwisu. Wszelkie uwagi i komentarze dotyczące serwisu mile widziane.

Tani dobry hosting w USA – HostMonster

O tym, że nie ma w Polsce ofert hostingowych o przyzwoitym stosunku cena do możliwości, chyba nie muszę nikogo przekonywać.

Osobiście korzystam z usług dzielonego hostingu na NetArt i jestem względnie zadowolony, jednak limity typu 10gb pojemności konta czy brak dostępu przez ssh są dosyć irytujące.

Kiedyś przez 2 lata korzystałem z usług DreamHost.com, jednak 2-letni promocyjny okres się skończył a dosyć wysoka awaryjność ich serwerów (tempest, gambino) skutecznie zniechęciła mnie do przedłużania umowy.

Po niedługim czasie od wyprowadzki z DreamHost’a zatęskniłem za ssh i parametrami „Unlimited”, zacząłem mały research i moją uwagę przykuł mało znany w Polsce HostMonster.

Serwis hostingowy z typu „amerykańskich gigantów” za cenę $5,95 miesięcznie oferuje naprawdę dużo. Na HM user ma gwarantowane max. 64mb pamięci RAM i 30% mocy procesora. Mi osobiście szczególnie spodobała się obsługa baz danych PostgreSQL, gdyż jest to SZBD, który najbardziej przypadł mi do gustu i głównie na nim opieram swoje aplikacje. (Dreamhost jak i wiele topowych firm hostingowych oferuje jedynie MySQL.)

Muszę przyznać, że HostMonster oferuje nieco mniej fajerwerków niż DreamHost chociażby limit na instalacje niektórych programów unixowych, brak SVN i pewnie jeszcze kilka niekoniecznie ważnych ficzerów. Brak tych wszystkich funkcjonalności rekompensuje stabilność działania serwera a to przecież jest najważniejsze.

Po około trzech miesiącach użytkowania jestem bardzo zadowolony z ich usług, wszystko działa należycie, nie było żadnych padów a support szybciutko odpowiadał na pytania.

Zatem polecam HostMonster jako tani dzielony hosting dla serwisów nie wymagających mega małych pingów. Abonament za hosting można obniżyć do bajecznie niskich $3,95 na miesiąc korzystając z wielu dostępnych w necie HostMonster coupon codes. Za max. 150zł rocznie mocny, stabilny hosting z większością parametrów „unlimited” i dostępem ssh… dla mnie rewelacja.