Autor Zpráva
Petr Ká
Profil
Ahoj,

potřeboval bych poradit, jak vytvořit výpis konverzací (jako na FB), kde nadpis je uživatel, s kterým jsem si psal a náhled je poslední zpráva, která je v daném chatu (bud ode mě, nebo od něj)

DB Struktura je následující:
Uživatelé:
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(64) NOT NULL,
  `pass` varchar(256) NOT NULL,
  `mail` varchar(128) NOT NULL,
  `first_name` varchar(32) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_action` timestamp NULL DEFAULT NULL,
  `blocked` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

Zprávy - chat
CREATE TABLE IF NOT EXISTS `messages` (
  `message_id` int(11) NOT NULL AUTO_INCREMENT,
  `from_uid` int(11) NOT NULL,
  `to_uid` int(11) NOT NULL,
  `readed` tinyint(4) NOT NULL DEFAULT '0',
  `sended` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `message` varchar(512) NOT NULL,
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=253 ;

Potřebuji získat (z "messages právě tu poslední zprávu"):
"Se Standou" => users.user_id, users.first_name, users.last_name, messages.message, messages.sended, messages.readed
"S Honzou" => users.user_id, users.first_name, users.last_name, messages.message, messages.sended, messages.readed


Pořád se v tom nějak motám a nedostávám informace, které potřebuji...

Děkuji za rady
Kajman
Profil
Tohle zjistí poslední komunikaci
SELECT Max(last_id) last_id,
       user_id
FROM   (SELECT Max(message_id) last_id,
               from_uid        user_id
        FROM   messages
        WHERE  to_uid = 42
        UNION ALL
        SELECT Max(message_id) last_id,
               to_uid          user_id
        FROM   messages
        WHERE  from_uid = 42) m
GROUP  BY user_id

Stačí to tedy obalit a přilinkovat data uživatele a text zprávy.
Petr Ká
Profil
Kajman:

Díky za reakci.

Ok, to zdá se funguje, jak má, jen mi není moc jasné jak tam dostat ty UID (from_uid nebo to uid) uživatelů, s kterýma jsem si kdy psal (nejlépe seřazeno dle aktuálnosti (messages.sended) a s LIMITem),

Při-JOINout si ty data pak už umím, jen ty UID mi nejsou jasné
Kajman
Profil
Tak tedy verze i s joiny.
SELECT u.*,
       m.*
FROM   (SELECT Max(last_id) last_id,
               user_id
        FROM   (SELECT Max(message_id) last_id,
                       from_uid        user_id
                FROM   messages
                WHERE  to_uid = 42
                GROUP  BY from_uid
                UNION ALL
                SELECT Max(message_id) last_id,
                       to_uid          user_id
                FROM   messages
                WHERE  from_uid = 42
                GROUP  BY to_uid) x
        GROUP  BY user_id) t
       JOIN messages m
         ON t.last_id = m.message_id
       JOIN users u
         ON t.user_id = u.user_id
ORDER  BY m.sended DESC
LIMIT  20  
Petr Ká
Profil
Tak jsem to vyřešil metodami pokus / omyl a funguje:

SELECT datas.uid,
       datas.message,
       datas.date,
       Concat(conv.first_name, "", conv.last_name) AS conversation_name
FROM   (SELECT m.message_id,
               m.message,
               IF (m.from_uid = 1, m.to_uid, m.from_uid) AS uid,
               m.readed,
               m.sended                                  AS `date`
        FROM   users u
               LEFT JOIN messages m
                      ON m.from_uid = u.user_id
        WHERE  m.message_id IN (SELECT Max(message_id)
                                FROM   messages
                                WHERE  to_uid = 1
                                        OR from_uid = 1
                                GROUP  BY Least(from_uid, to_uid),
                                          Greatest(from_uid, to_uid))) datas
       LEFT JOIN users conv
              ON conv.user_id = datas.uid 



Kajman:

Ta tvá verze nevrací všechny konverzace, které jsou v DB. Vrátí pouze jednu (nad testovacímy daty by mi měla vrátit 3)
Kajman
Profil
Aha, chyběly tam group by, doplněny do [#4]

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: