0
I am modeling a basis for message exchange between users, and I have the following tables, user, user_friend and user_friend_message.
The table user stores all system users, user_friend makes the friendship link between two users and finally user_friend_message that saves the exchange of messages between two users.
On the friendship link in the table user_friend have the columns id_user and id_friend, the user sending the invitation has the id stored in the column id_user and his friend in id_friend and vice versa.
My problem is a screen that lists all contacts/friends and the display order should be first contacts with unread messages sent and then users with read messages or no messages.
Message exchange follows the same logic as the friendship link. The user sending the message has the ID stored in the column id_user and what it receives in the id_friend. Messages between two users will always be displayed on the same screen (facebook chat style, Hangout etc.) and regardless of whether the user sent or received the message (id_user or id_friend)
The modeling is very confusing for me and I don’t know if I can do this with just one query. The query below queries every user friendship link and sorts in descending order by the date of sending the message, but does not return the result I need.
SELECT
u.id
FROM
`users_friends` uf
INNER JOIN
`users` u ON u.id = uf.id_user
LEFT JOIN
`users_friend_message` m ON m.id = m.id_user
WHERE
(uf.id_friend = :id_user OR uf.id_user = :id_user) AND uf.status = 1
ORDER BY
m.date_send DESC
You will have to add a new column in the table
user_friend_messagewho will be responsible for saying whether the message has been read or not. To style the Facebook chat, you can implement a JS script that will detect clicks in the chat window and, if there were unread messages, will send an update via AJAX to mark the message as read.– Clayderson Ferreira
The problem is not to mark the message as read or unread, but to mount a query that lists all a user’s contacts even if they already have conversations or not. If you have unread conversations the user will be at the top of the contacts in the sent message order. I quoted facebook to understand more or less how I need the screen to be displayed. In my case the conversation will not be updated in real time, in fact it looks more like an email reader with the difference of having to list all contacts (conversation or not)
– Dagobe