Miałem ostatnio trochę czasu (a może raczej nie miałem czasu, ale byłem zmuszony), żeby poczytać sobie dlaczego ten postgresql taki zły i niefajny.
Pierwsza rzecz jest taka, że ten feature w MySQLu, to tak naprawdę bug z punktu widzenia standardu SQL. Dlaczego?
Przypuśćmy, że mamy tabele users i opinions tak jak w powyższym przykładzie. Dla skupienia uwagi będę wrzucał kod SQL, żeby każdy mógł się pobawić sam.
[code]create table users
(
id int primary key,
login varchar(255)
);
create table opinions
(
id int primary key,
title varchar(255),
created_at date,
user_id int references users(id)
);
insert into users VALUES(1, ‘Dziabong’);
insert into users VALUES(2, ‘Charlie’);
insert into users VALUES(3, ‘Griffin’);
insert into users VALUES(4, ‘Cichy’);
insert into opinions VALUES(1, ‘Żałość’, ‘2009-02-01’, 1);
insert into opinions VALUES(2, ‘Wspaniałość!’, ‘2009-02-02’, 1);
insert into opinions VALUES(3, ‘Mogło być lepiej…’, ‘2009-02-03’, 2);
insert into opinions VALUES(4, ‘Nie wytrzymię!’, ‘2009-02-04’, 2);
insert into opinions VALUES(5, ‘Super.’, ‘2009-02-05’, 3);[/code]
Wypełniłem obie tabele danymi i można teraz zacząć testowanie.
Na początek najlepiej zobaczyć powiązania tych 2 kolumn:
[code]SELECT users., opinions.
FROM users
LEFT JOIN opinions ON opinions.user_id = users.id;
id | login | id | title | created_at | user_id
----±---------±—±--------------------±-----------±--------
1 | Dziabong | 2 | Wspaniałość! | 2009-02-02 | 1
1 | Dziabong | 1 | Żałość | 2009-02-01 | 1
2 | Charlie | 4 | Nie wytrzymię! | 2009-02-04 | 2
2 | Charlie | 3 | Mogło być lepiej… | 2009-02-03 | 2
3 | Griffin | 5 | Super. | 2009-02-05 | 3
4 | Cichy | | | |[/code]
Jak widać do użytkownika może być przypisane kilka opinii. Łatwo też zauważyć, że dane użytkowników mogą występować w wynikach kilkakrotnie. Należy o tym pamiętać podczas używania JOINów - jeżeli nie zostanie użyte GROUP BY, ani DISTINCT, to można dostać nie do końca to co się chciało. Ostatni wynik ma puste wartości dla tabeli opinions, jest to wynik zastosowania LEFT JOINa, który wybiera wszystkie rekordy z pierwszej tabeli i dorzuca do niej pasujące rekordy z drugiej tabeli niezależnie do tego czy takie pasujące rekordy istnieją.
Przejdźmy teraz do GROUP BY. Załóżmy, że chcemy wybrać wszystkich użytkowników posortowanych po dacie napisania ostatniej opinii. W MySQLu można zrobić coś takiego:
SELECT users.*, opinions.created_at
FROM users
INNER JOIN opinions ON opinions.user_id = users.id
GROUP BY opinions.user_id
ORDER BY opinions.created_at DESC;
MySQL
+----+----------+------------+
| id | login | created_at |
+----+----------+------------+
| 3 | Griffin | 2009-02-05 |
| 2 | Charlie | 2009-02-03 |
| 1 | Dziabong | 2009-02-01 |
+----+----------+------------+
PostgreSQL
ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function
MySQL wyrzuca niby jakiś wynik (aczkolwiek na pewno nie jest to czego oczekiwaliśmy), a PostgreSQL błąd. Dlaczego tak się dzieje?
Do każdego użytkownika może być przypisana więcej niż jedna opinia. Jeżeli grupujemy po user_id i jednocześnie chcemy wybrać created_at, to zachodzi problem - które created_at wybrać? Na przykład do użytkownika Dziabong przypisane są 2 opinie, o numerach id 1 i 2. Tylko jedno created_at może być wybrane i nie wiadomo właściwie które - w tym wypadku mysql wybiera nie to co chcieliśmy.
Zapytanie to powinno wyglądać tak:
[code]SELECT DISTINCT users.*, max(opinions.created_at) AS last_opinion_created_at
FROM users
INNER JOIN opinions ON opinions.user_id = users.id
GROUP BY users.id, users.login
ORDER BY last_opinion_created_at DESC;
id | login | last_opinion_created_at
----±---------±------------------------
3 | Griffin | 2009-02-05
2 | Charlie | 2009-02-04
1 | Dziabong | 2009-02-02[/code]
Wybieramy wartość maxymalną z pobieranych opinii (MAX(opinions.created_at)) i dopiero po tej wartości sortujemy.
Jest to wersja, która zadziała dla obu silników. Jedyny problem jest taki, że w przypadku postgresqla trzeba wymienić w GROUP BY wszystkie kolumny, które wymienione są w po SELECT. W tym przypadku nie jest to uciążliwe, ale z reguły kolumn jest dużo więcej, czasami nawet kilkadziesiąt. Jak to uprościć? Trzeba zastosować dodatkowe zapytanie w INNER JOIN:
[code]SELECT users.*, opinions.created_at
FROM users
INNER JOIN (
SELECT MAX(created_at) as created_at, user_id
FROM opinions
GROUP BY user_id
) AS opinions
ON users.id = opinions.user_id
ORDER BY opinions.created_at DESC;
id | login | created_at
----±---------±-----------
3 | Griffin | 2009-02-05
2 | Charlie | 2009-02-04
1 | Dziabong | 2009-02-02[/code]
W tym momencie grupowanie odbywa się w zagnieżdżonym zapytaniu, więc w zapytaniu “głównym” można bez problemu napisać SELECT users.*
Tak samo można to zrobić dla powyższego przykładu z COUNT:
[code]SELECT users.*, opinions.count
FROM users
INNER JOIN (
SELECT COUNT(id) as count, user_id
FROM opinions
GROUP BY user_id
) AS opinions
ON users.id = opinions.user_id
ORDER BY opinions.count DESC;
id | login | count
----±---------±------
1 | Dziabong | 2
2 | Charlie | 2
3 | Griffin | 1[/code]
W tym miejscu należy sobie zadać pytanie, czy to co robi mysql jest dobre, czy nie? Z jednej strony jest to niezgodne ze standardem SQL i łatwiej można popełnić błąd, bo mysql nie wyrzuci w takich wypadkach błędu, ale z drugiej strony pisania jest dużo mniej. Kilku ludzi pytało się na grupie postgresqla, dlaczego nie można byłoby tego uprościć i połączyć podejście obu silników. Wyrzucać błąd jeżeli ktoś chce wybrać kolumnę, która może wystąpić więcej niż 1 raz i nie występuje w GROUP BY. Przecież w powyższym przykładzie grupujemy po user_id, więc login i id nie mogą wystąpić 2 razy, spokojnie można byłoby je pominąć w GROUP BY! Kłopot w tym, że niestety silnik bazodanowy nie myśli i ciężko byłoby zaimplementować analizowanie tabel użytkownika w celu stwierdzenia, czy dana kolumna jest w wynikach wyszukiwania unikalna.
Na początku też pomyślałem: standardy standardami, ale lepsze jest często podejście pragmatyczne i lepiej to uprościć. Kiedy jednak wklepałem trochę linijek w konsoli postgresa stwierdziłem, że takie zapytania jak te 2 ostatnie, z zagnieżdżonymi zapytaniami są czytelniejsze. Naprawdę 
Polecam w aplikacjach ten sposób formułowania zapytań - dzięki temu uzyskujemy kompatybilność z oboma silnikami.
Jedyny minus jest taki, że kod działający w MySQL jest krótszy:
User.find(:all,
:select => "users.*, COUNT(opinions.id) AS opinions_count",
:joins => :opinions,
:group => "opinions.user_id",
:order => "opinions_count DESC")
niż:
User.find(:all,
:joins => "INNER JOIN (
SELECT COUNT(id) as count, user_id
FROM opinions
GROUP BY user_id
) AS opinions
ON users.id = opinions.user_id,
:order => "opinions.count DESC")
Niektórzy powiedzą może, że nie dość, że pierwsza wersja jest krotsza, to jeszcze czytelniejsza, ale z perspektywy pokodzenia trochę w czystym SQLu wydaje mi się, że jednak druga wersja jest bardziej logiczna i czytelna.
Myślałem też nad zmodyfikowaniem nieco AR (może sequel albo datamapper już mają takie bajery?), żeby nie trzeba było dodawać tego całego kodu SQL, tylko napisać coś podobnie prostego do :joins => :opinions.
Co sądzicie?
