mambo Jambo mambo Jambo - 1 year ago 58
MySQL Question

Getting the last message between users like facebook

I have a messaging app that I want to display messages like facebook, it should get the last send message by either the sender or recipient, my table layout is like this:


|id | user1Fk| user2Fk |subject | user1Delete | user2Delete | dateCreated |

and my

| id | first_name | last_name | image |

my Query

SELECT `id` , `user1Fk` as sender_id,
(SELECT concat(first_name,\" \",last_name)
FROM user_tbl
WHERE = sender_id
) as senderName,
`user2Fk` as recipient_id ,
(SELECT concat(first_name,\" \",last_name)
FROM user_tbl
WHERE = recipient_id
) as recipientName,
(SELECT image
FROM user_tbl
WHERE = sender_id
) as senderImage,
(SELECT image
FROM user_tbl
WHERE = recipient_id
) as recipientImage,
`subject`, `message`, `user1Delete`, `user2Delete`,
FROM `message_tbl`as m1
WHERE dateCreated = (SELECT MAX(m2.dateCreated)
from message_tbl as m2
WHERE (m1.user1Fk = m2.user1Fk
AND m1.user2Fk = m2.user2Fk
OR m1.user1Fk = m2.user2Fk
AND m1.user2Fk = m2.user1Fk
) AND ? IN (m1.user1Fk, m1.user2Fk)
ORDER BY dateCreated DESC

This query is working for the most part but its lacking, I want it to check, if the given id by the
it should then check if
is a 0 or 1, if its a 1 then do not display the message
user 1
deleted it, the same with user 2 but I cannot think of the logic, can anyone help me?

Answer Source

I'm assuming that the question mark is there because this is a prepared query and the question mark should be replaced with a user id.

Your query is a little bit over complicated because there are a lot of subselects. I tried to improve it a bit for better performance and to be able to extend it easier. With SQL you can join tables instead of using subqueries, this is better for performance, and it makes it easier to add conditions to the joined tables.

In the example below I added a condition for both the user1 and user2 delete fields to be 0. I set the limit to 1, which means only the first row will be returned. Together with the descending order of the date, this means that only the newest message will be returned. I also changed the question mark to :user_id because in this example it is used twice and this way you'll only have to add it once.

SELECT, AS sender_id, 
    CONCAT(u1.first_name, ' ', u1.last_name) AS senderName, AS recipient_id, 
    CONCAT(u2.first_name, ' ', u2.last_name) AS recipientName,
    u1.image AS senderImage,
    u2.image AS recipientImage,
FROM messages_tbl m
INNER JOIN user_tbl u1 ON m.user1Fk =
INNER JOIN user_tbl u2 ON m.user2Fk =
WHERE (m.user1Delete = 0 AND = :user_id)
OR (m.user2Delete = 0 AND = :user_id)
ORDER BY m.dateCreated DESC

I haven't been able to test it but I hope this is what you were looking for.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download