mambo Jambo mambo Jambo - 9 months ago 28
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:

messages_tbl


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


and my
user_tbl


_______________________________________
| id | first_name | last_name | image |


my Query

SELECT `id` , `user1Fk` as sender_id,
(SELECT concat(first_name,\" \",last_name)
FROM user_tbl
WHERE user_tbl.id = sender_id
) as senderName,
`user2Fk` as recipient_id ,
(SELECT concat(first_name,\" \",last_name)
FROM user_tbl
WHERE user_tbl.id = recipient_id
) as recipientName,
(SELECT image
FROM user_tbl
WHERE user_tbl.id = sender_id
) as senderImage,
(SELECT image
FROM user_tbl
WHERE user_tbl.id = recipient_id
) as recipientImage,
`subject`, `message`, `user1Delete`, `user2Delete`,
`dateCreated`
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
?
matches
user1Fk
it should then check if
user1Delete
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 
    m.id, 
    u1.id AS sender_id, 
    CONCAT(u1.first_name, ' ', u1.last_name) AS senderName, 
    u2.id AS recipient_id, 
    CONCAT(u2.first_name, ' ', u2.last_name) AS recipientName,
    u1.image AS senderImage,
    u2.image AS recipientImage,
    m.subject, 
    m.message, 
    m.user1Delete, 
    m.user2Delete, 
    m.dateCreated
FROM messages_tbl m
INNER JOIN user_tbl u1 ON m.user1Fk = u1.id
INNER JOIN user_tbl u2 ON m.user2Fk = u2.id
WHERE (m.user1Delete = 0 AND u1.id = :user_id)
OR (m.user2Delete = 0 AND u2.id = :user_id)
ORDER BY m.dateCreated DESC
LIMIT 1

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