Forum PHP.pl

Witam, wiem że temat był dużo razy poruszany ale nadal nie potrafię zastosować go do swoich potrzeb.
Mam tabelę:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `wiadomosci` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grupa` int(11) NOT NULL,
  `wiadomosc` varchar(2048) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `from_id` int(11) NOT NULL,
  `to_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
 
 
INSERT INTO `wiadomosci` (id, grupa, wiadomosc, from_id, to_id) VALUES (`1`, `1`, `Witam`,`20`,`30`);
INSERT INTO `wiadomosci` (id, grupa, wiadomosc, from_id, to_id) VALUES (`2`, `1`, `Witam2`,`20`,`30`);
INSERT INTO `wiadomosci` (id, grupa, wiadomosc, from_id, to_id) VALUES (`3`, `2`, `Witam3`,`22`,`30`);
INSERT INTO `wiadomosci` (id, grupa, wiadomosc, from_id, to_id) VALUES (`4`, `2`, `Witam4`,`30`,`22`);
INSERT INTO `wiadomosci` (id, grupa, wiadomosc, from_id, to_id) VALUES (`5`, `2`, `Witam5`,`22`,`30`);
[SQL] pobierz, plaintext 

i chcę wyciągnąć najświeższą wiadomość dla id uzytkownika="30" , gdzie grupowanie jest po polu "grupa", a najświeższa wiadomość to ta, która ma największe "id".

[SQL] pobierz, plaintext 
SELECT wiadomosc FROM wiadomosci WHERE from_id=30 OR to_id=30 GROUP BY grupa ORDER BY id DESC
[SQL] pobierz, plaintext 

wyciąga mi

[PHP] pobierz, plaintext 
Witam
Witam3
[PHP] pobierz, plaintext 

a potrzebne

[PHP] pobierz, plaintext 
Witam2
Witam5
[PHP] pobierz, plaintext 

jakiś pomysł?

Dlaczego potrzebujesz grupowania?

Używanie w select nazw kolumn, które nie są objęte ani funkcją agregującą ani nie są w group by jest dozwolone w mysql, ze względów wydajnościowych, ale nie jest to poprawna składnia tradycyjnego SQL. Na przykład próbując takie samo zapytanie w MSSQL dostałbyś znany chyba wszystkim, którzy z sql serverem mieli do czynienia błąd:
Column 'wiadomosc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Mysql pozwala na takie rzeczy, takie kolumny nazywając ukryte (hidden), ale wartość, którą zwraca select jest pewna tylko jeśli wszystkie wiersze w obrębie tej samej grupy mają ją identyczną.
http://dev.mysql.com/doc/refman/5.6/en/gro...en-columns.html

W twoim przypadku, ponieważ kolumna wiadomosc nie występuje w group by (tam jest tylko grupa), nie wykonujesz też na tej wiadomości nic takiego jak sumowanie, zliczanie, czy cokolwiek z listy funkcji agregujących http://dev.mysql.com/doc/refman/5.6/en/gro...-functions.html, nie możesz mieć absolutnie żadnej pewności z którego rekordu mysql zwróci wartość kolumny wiadomosc.

Żeby stwierdzić jak dokładnie musisz zbudować zapytanie musisz najpierw precyzyjnie odpowiedzieć na pierwsze pytanie.

Ten post edytował pmir13 27.04.2011, 20:36:53

Mysql w takich wypadkach jak podałeś zwraca wartość kolumny dla pierwszego wiersza danej grupy. To co chcesz więc osiągnąć, to ustawić wiersze we własciwej kolejności zanim dojdze do grupowania. Jako że ORDER BY wykonuje się dopiero po GROUP BY, to musisz do grupowania wysłać podzapytanie z posortowanymi wynikami. W tym wypadku musisz więc "odwrócić" tabelę w podzapytaniu i potem ją pogrupować.

A co do celu, to chyba jest taki, by użytkownik widział najświeższe wiadomości związane z sobą (stąd sprawdzanie from_id i to_id) w każdej z grup.

Przecież to kompletnie przeczy temu co można przeczytać w manualu mysqla, do którego link podałem. I to dokładny link, omawiający właśnie taką sytuację. Naprawdę tak trudno sprawdzić?

Można mówić - u mnie na moim systemie, na mojej wersji mysql, teraz zwracany jest pierwszy wiersz z grupy. Nic więcej.

http://bugs.mysql.com/bug.php?id=60735
Tutaj mamy bardzo konkretny przykład - ktoś był przekonany o tym, że właśnie pierwszy wiersz powinien być zawsze zwracany, oparł na tym założeniu swój system, robiąc dokładnie to co proponujesz, najpierw sortował, a potem grupował, po czym jakież było jego zdziwienie, gdy po przejściu na inną wersję okazało się, że zwracany jest ostatni wiersz zamiast pierwszego. Zgłosił więc bug, ale dostał bardzo krótką odpowiedź, odsyłającą do wyżej wspomnianego linku.

pmir: To że przeczy dokumentacji, nie znaczy że MySQL tak się nie zachowuje. Pracowałem z wersjami od 3.X do 5.X i za każdym razem tak się zachowywał. Moje więc zdanie wynika nie tyle z czytania dokumentacji, co realnego i praktycznego implementowania zapytań. Możliwe, że któraś z (od którejś) wersji zachowuje sie inaczej, ponieważ nie wszystkie na kompie miałem i testowałem. Jak na razie jednak zachowuje się w ten sposób od kilku wersji, więc trudno nie mieć wrażenia, że jednak tutaj determinizm przez ogromnie długi czas występuje, nawet mimo faktu, że nie jest zgodny z dokumentacją. Jakkolwiek dzięki za cynk, przyjrzę się w swoich systemach, czy aby nie zafałszowuje mi to gdzieś wyniku. Póki co bowiem wszystko działa w porządku.

Sam fakt, że taka składnia nie przejdzie na sql serverze czy oracle oznacza też że są inne, bardzo wydajne sposoby uzyskiwania potrzebnych danych.
Wracając jednak do tego o co chodzi prawdopodobnie autorowi wątku, najrozsądniejsze wydaje się być najpierw prawidłowe pogrupowanie:

[SQL] pobierz, plaintext 
SELECT grupa, max( id ) AS najswiezsza
FROM wiadomosci
WHERE from_id =30 OR to_id =30
GROUP BY grupa
[SQL] pobierz, plaintext 

Obie kolumny wchodzące do selecta są prawidłowe, bo grupa jest w GROUP BY, a max jest funkcją agregującą.
To jest bardzo szybkie zapytanie przy prawidłowo ustawionych indeksach. Choćby wiadomości było miliony where na starcie ograniczy to do wiadomości dotyczących pojedynczego użytkownika, a rezultat to bardzo mała tabela, zawierająca tylko tyle rekordów ile jest różnych grup (ile może być grup wiadomości?).
Teraz mając najświeższe wiadomości dla danej grupy możemy połączyć to z powrotem z tą samą tabelą po to by wydobyć dane wiadomości korzystając z faktu, że id jednoznacznie identyfikuje rekord, więc łaczymy po prostu po id<->najswiezsza. Łączymy kilka rekordów z ewentualnie bardzo dużą tablicą, ale po kluczu primary, czyli w najszybszej konfiguracji, więc również będzie bardzo szybko.
Ostatecznie otrzymujemy zapytanie, które będzie sprawnie działać na BARDZO dużej bazie wiadomości:

[SQL] pobierz, plaintext 
SELECT wz.wiadomosc FROM 
(    SELECT w.grupa, max( id ) AS najswiezsza
     FROM wiadomosci w
     WHERE w.from_id =30 OR w.to_id =30
     GROUP BY w.grupa ) w
JOIN wiadomosci wz
ON wz.id = w.najswiezsza
[SQL] pobierz, plaintext