MySQL: DISTINCT 2 rows...

7 naročnikov

Če se mi kdaj zaplete pri programiranju, pa je to pri kakšnem bolj zahtevnem MySQL stavku in zato vas prosim za pomoč.

Imam tabelo zasebna_sporocila, ki ima takšno strukturo:

id | fromuser | touser | message | date
1 | 1 | 3 | blablabla | 1043684100
2 | 1 | 2 | blablabla | 1049146320
3 | 2 | 1 | blablabla | 1049146920
4 | 3 | 1 | blablabla | 1049147100
5 | 2 | 3 | blablabla | 1049153340
6 | 1 | 2 | blablabla | 1049627280

Z navadnim DISTINCT ukazom (SELECT DISTINCT fromuser, touser FROM zasebna_sporocila) dobim takšen rezultat:

fromuser | touser
1 | 3
1 | 2
2 | 1
3 | 1
2 | 3

Problem pa je, ker bi rad dobil unikatne poizvedbe. V zgornjem rezultatu se prikaže 1 2 in 2 1 - kar bi želel, da se prikaže kot en rezultat, saj bi rad dobil zgrupirana sporočila med dvema uporabnikoma. Nekako podobno kot ima to narejeno Facebook v zasebnih sporočilih.

Nekam bi moral še dodati poizvedbo WHERE (fromuser = 1 OR touser = 1) - rezultat za uporabnika, ki ima id 1.

Seveda moram potem na koncu rezultate urediti po datumu (ORDER BY date DESC).

Kakršnakoli pomoč več kot dobrodošla! Zakomplicirano? :)

poglej za GROUP BY, v tvojem primeru pred ORDER BY das GROUP BY(to_user) verjetno

probaj s tem:

SELECT *
FROM zasebnasporocila
WHERE (senderid=$id1 OR receiverid=$id1)
AND (senderid=$id2 OR receiver_id=$id2)
ORDER BY date

@boskar tvoj primeru bi prišel prav, če bi že imel id od ostalih članov s katerimi je dotični član izmenjal sporočila. Jaz pa želim dobiti vsa ID sporočila, v katerih je sodeloval en uporabnik (npr. id = 1). Problem pa je, ker ima lahko taisti uporabnik (id = 1) svoj id vpisan v row fromuser ali pa touser.

@Mešetar mislim, da mi group by ne bo prišel prav...

Takšne poizvedbe nikoli ne boš mogel zadovoljivo optimizirati in bo po vsej verjetnosti trajala preveč časa in ti s tem ubijala strežnik, ko boš imel teh sporočil več. Jaz bi na tvojem mestu dodal še eno tabelo, ki bi vsebovala ID uporabnika, ID sporočila, mogoče še timestamp sporočila, da bo tudi ORDER BY lahko uporabil indeks. V to tabelo ob dodajanju vsakega sporočila dodaš dva zapisa, v enega zapišeš ID od fromuser, v drugega ID od touser, v oba pa isti ID sporočila in isti timestamp.

Tole dodajanje dveh zapisov lahko tudi avtomatiziraš z uporabo triggerjev.

Poizvedba je potem enostavna:

SELECT
      zasebna_sporocila.*
   FROM
      zasebna_sporocila_pomozna,
      zasebna_sporocila
   WHERE
      zasebna_sporocila_pomozna.user_id = 1 AND
      zasebna_sporocila.id = zasebna_sporocila_pomozna.sporocilo_id
   ORDER BY
      zasebna_sporocila_pomozna.datum;

Če v tabeli zasebnasporocilapomozna nastavis indeks na polji user_id in datum, ga bo ta poizvedba tudi uporabila in bo zato tudi seveda super hitra. :)

Če mogoče ni najbolj jasno zakaj je tu potreben tudi indeks na polju datum, sem o tem nekoč davno pisal na svojem sicer že davno mrtvem blogu, pa da se ne ponavljam, je tukaj kar link: SQL in indeksi (2.del)

v tem primeru naredis vedno 2 poizvedbe, ena za FROM users, druga za TO user.... in jih groupiras poo menjenih indexih...

ni mi pa jasno, kaj je tvoj namen?

ce bi rad to delal s sporocili, bi moral voditi idsporocila (root) in potem vsa pripeta sporocila k temu (parentid) ... ce gre za sporocila tipa pri FBju ... ali forumu (ali gmailu)...

ce gre za normalen chat pa je zadosti where fromid = 1 OR fromid=2 OR toid=1 OR toid=2

@Vini mogoče je tvoja rešitev res najbolj primerna. Bolj me skrbi, da bom moral potem preurejati še ostalo kodo, da bo v primeru izbrisa uporabnika ali pa izbrisa kakšnega sporočila potrebno narediti tudi en nov sql stavek, ki bo urejat tisto pomožno tabelo.
Navsezadnje mi je pomembno le da dobim zadnji ID tabele, ki sta je 2 uporabnika uporabljala za chat.

@Mešetar

ce bi rad to delal s sporocili, bi moral voditi idsporocila (root) in potem vsa pripeta sporocila k temu (parentid) ... ce gre za sporocila tipa pri FBju ... ali forumu (ali gmailu)...

To pa je predlagal Vini v svojem postu - če sem te prav razumel.

Lucifix, tudi brisanje sporočila in hkratno brisanje zapisov v pomožni tabeli lahko urediš s triggerji.

Thx Vini, grem na delo, ker bo to verjetno kar dobra rešitev ;)

Kaj pa to? :)

SELECT
    IF(from_user>to_user, from_user, to_user) AS user1,
    IF(from_user>to_user, to_user, from_user) AS user2,
    `date`
FROM `zasebna_sporocila`
GROUP BY user1, user2
ORDER BY `date` DESC

EDIT:
namesto IF-ov lahko uporabiš

GREATEST(from_user, to_user) AS user1,
LEAST(from_user, to_user) AS user2,