hello,
im trying to make a DB for a message system.
the best way i have made is this:
*TABLE conversations * (informacion de cada conversacion)
.................................
i*d_conversation (bigint)
count (smallint) updated every time a new message is
added to this conversation
lastmessagetime (timestamp) **updated every time a new message is added to
this conversation**
*
*INDEX (id_conversation,lastmessage)**
*
*
**TABLE user_conversations* (relacion usuario-conversacion)
..........................................
*id_user (int)
id_conversation (bigint)
**read (bool)
**
**INDEX (id_conversation)
INDEX (id_user)*
*TABLE messages * (mensajes on every conversation)
...........................
id_conversation (bigint)
id_writer (int)
message (varchar)
time (timestamp)
INDEX (id_conversation,time)
my problem is that i need to query those things and i dont know how to do
that.
"*Conversation list of a given user with `read`, number of messages
(`count`) , date of last message (i store this value on `conversation`
table) and who did it
Order by time of the last message of each conversation*"
i have tried everything i could but im not getting a good solution:
SELECT UC.id_conversation, UC.read, C.count, UNIX_TIMESTAMP( ) -
UNIX_TIMESTAMP( lastmessage ) , M.message, M.id_usr
FROM user_conversations UC, conversations C, messages M
WHERE UC.id_usr = 1
AND C.id_conversation = UC.id_conversation
AND M.id_conversation = UC.id_conversation
AND M.time = C.lastmessage
ORDER BY C.lastmessage DESC
LIMIT 0,10
thats giving me an ALL in `conversations` as result
id select_type table type possible_keys key
key_len ref rows
Extra
1 SIMPLE UC ref id_conversation,id_usr id_usr
4 const 3
Using temporary; Using filesort
1 SIMPLE M ref orderbytime
orderbytime 8 netlivin3.UC.id_conversation 1
1 SIMPLE C ALL id_conversation NULL
NULL NULL 3 Using
where
im completely lost here, ill really appreciate any help.
thanks.
