Unikalne pary Opcje

[markonix]

Bardzo prosty problem.

Mamy tabelę z wiadomościami - id odbiorcy i id wysyłającego.
Chcę stworzyć listę konwersacji czyli po jednej wiadomości z każdej konwersacji.

1 | 2
1 | 2
1 | 3
3 | 1
2 | 1

Wynik oczekiwany dla useraID: 1 - po jednej, ostatniej wiadomości z każdej rozmowy.

2|1
1|3

Niby proste ale wszystkie moje wariacje SELECTa zwracają ładnie po jednej wiadomości ale dla rozmów gdzie była odpowiedź zwraca 2 wiersze 1|2 i 2|1 traktując to jako osobne rozmowy.

Jedna z prób:

[SQL] pobierz, plaintext 
SELECT t.* FROM
            (SELECT * FROM `ci_messages`
                WHERE `sender_id` = 1 OR `receiver_id` = 1
                ORDER BY `ci_messages`.`date` DESC) t
            GROUP BY t.`sender_id`, t.`receiver_id`
            ORDER BY `date` DESC
[SQL] pobierz, plaintext 

Jak wytłumaczyć bazie że 1|2 to dla mnie to samo co 2|1 i nie chcę aby się powtarzało?

Inna próba z takim samym rezultatem:

[SQL] pobierz, plaintext 
SELECT * 
FROM (
    SELECT * FROM `ci_messages` WHERE `sender_id` = 1 ORDER BY `ci_messages`.`date` DESC
) AS sub
GROUP BY `receiver_id` 
UNION
SELECT * 
FROM (
    SELECT * FROM `ci_messages` WHERE `receiver_id` = 1 ORDER BY `ci_messages`.`date` DESC
) AS sub
GROUP BY `sender_id` 
[SQL] pobierz, plaintext 

Ten post edytował markonix 16.11.2013, 23:21:45

[werdan]

http://sqlfiddle.com/#!2/e9642/2

[SQL] pobierz, plaintext 
SELECT DISTINCT sender_id, receiver_id FROM msg
[SQL] pobierz, plaintext 

Coś takiego pasi?

[markonix]

To była moja pierwsza próba i faktycznie pokazuje w porządku pary, ale tak jak napisałem na początku chce pobrać pary wiadomości czyli całe wiersze.
Tutaj zobrazowany problem z prostym DISTINCT:

http://sqlfiddle.com/#!2/84c5ea/1/0

[redeemer]

Może tak:

[SQL] pobierz, plaintext 
SELECT  
	IF(
		sender_id<receiver_id,
		concat(sender_id,'_',receiver_id),
		concat(receiver_id,'_',sender_id)
	) AS custom_id,
 
	text,
	sender_id,
	receiver_id
FROM msg
	GROUP BY custom_id
[SQL] pobierz, plaintext 

Edit: Hmmm, doszedłem do wniosku, że chodziło Ci chyba o coś innego (modyfikacja Twojego zapytania):

[SQL] pobierz, plaintext 
SELECT t.* FROM
(SELECT * FROM ci_messages WHERE sender_id = 1 OR receiver_id = 1 ORDER BY ci_messages.id DESC) t
WHERE t.sender_id=1 
GROUP BY t.sender_id, t.receiver_id
ORDER BY t.id DESC
[SQL] pobierz, plaintext 

http://sqlfiddle.com/#!2/39580a/7

Ten post edytował redeemer 17.11.2013, 14:52:31

[markonix]

No właśnie zanim zeedytowałeś zacząłem przerabiać Twoją pierwszą propozycję, która była w porządku ale używała tego dziadowskiego GROUP BY, którego nie da się posortować przed grupowaniem.

Co do drugiej propozycji już na wstępie widzę problem, że nie widzi rozmowy o id 7 - "nowszej" od tej o ID 6.

Tak więc druga Twoja propozycja ma chyba ten sam problem co moje pierwsze próby.

Druga pomysłowa, nie wpadłbym na użycie CONCAT (IMG:style_emoticons/default/smile.gif)
Problem tylko z sortowaniem tak więc nie ma innej opcji jak sub zapytanie.

[SQL] pobierz, plaintext 
SELECT t.*
FROM
  ( SELECT IF( sender_id<receiver_id, concat(sender_id,'_',receiver_id), concat(receiver_id,'_',sender_id) ) AS custom_id,
           ci_messages.*
   FROM ci_messages
   WHERE `sender_id` = 1
     OR `receiver_id` = 1
   ORDER BY `date` DESC ) t
GROUP BY custom_id
ORDER BY `date` DESC
[SQL] pobierz, plaintext 

[redeemer]

Ok, w końcu zrozumiałem o co tak naprawdę chodzi (IMG:style_emoticons/default/smile.gif)

Zapytanie bez IF i CONCAT będzie bardziej "eleganckie":

[SQL] pobierz, plaintext 
SELECT t.*
FROM
  ( SELECT * FROM ci_messages WHERE `sender_id` = 1 OR `receiver_id` = 1 ORDER BY `id` DESC ) t
GROUP BY LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id)
ORDER BY `id` DESC
[SQL] pobierz, plaintext 

Warto pamiętać, że grupowanie po "sztucznych" kolumnach nie korzysta z indeksów i jeśli to zapytanie ma być często wykonywane, może warto rozważyć dodanie kolejnej kolumny do tabeli (+odpowiednie indeksowanie).

Jestem też prawie pewien, że bez ruszania tabeli da się to zrobić lepiej, ale nie mam ostatnio za dużo wolnego czasu, żeby się bardziej przyjrzeć (IMG:style_emoticons/default/smile.gif)

[markonix]

Rozumiem, że sztuczne kolumny odnoszą się do rozwiązania z concat czy chodzi Ci o Twoją aktualną propozycję z podzapytaniem?

[redeemer]

Te dwie propozycje wydajnościowo powinny być tożsame (ewentualnie minimalnie większy narzut pamięci będzie miało zapytanie z użyciem concat). Druga propozycja jednak, ma moim zdaniem "ładniejszy" kod.

[markonix]

No tak, ale wspomniałeś o tymczasowych kolumnach i ich wydajności.
Faktycznie zapytanie będzie często wykonywane (i jego wariacje) dlatego będę je odchudzał jak tylko się da.

W rozwiązaniu z CONTACT takową kolumną jest custom_id.
W Twoim najnowszym rozwiązaniu nie ma dynamicznie tworzonej kolumny więc wg tego co napisałeś wcześniej powinno być lepsze (pomijam fakt, że lepiej wygląda wizualnie (IMG:style_emoticons/default/wink.gif)