I am modeling a base for exchanging messages between users, and I have the following tables, user , user_friend and user_friend_message .
The user table stores all users of the system, user_friend makes the friendship connection between two users and finally user_friend_message that saves the exchange of messages between two users.
In the friendship link in the user_friend table I have the columns id_user and id_friend , the user who sends the invitation has the id stored in the id_user column and his friend in the id_friend and vice versa. / p>
My problem consists of a screen that lists all contacts / friends where the display order should be first contacts with messages sent unread and then users with messages read or no messages.
The exchange of messages follows the same logic as the friendship link. The user who sends the message has the ID stored in the id_user column and the one that receives the id_friend . The messages between two users will always appear 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 do not know if I can do this with just one query. The query below queries the user's entire friendship link and sorts in descending order the date the message was sent, but it 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