Reputation: 151
Please tell me what is wrong with this code, this returns all the conversations, but it returns latest message for only first conversation.
SELECT
conversations.*,
messages.message,
patients.first_name as fullname,
patients.city,
patients.thumb,
patients.gender,
patients.online_status
FROM
patients
INNER JOIN conversations
ON conversations.patient_id_fk = patients.id
LEFT JOIN messages
ON messages.conversation_id = conversations.id
AND messages.message_id =
(
SELECT MAX(message_id)
FROM messages z
WHERE z.therapist_id_fk = conversations.therapist_id_fk
)
WHERE conversations.therapist_id_fk='1'
GROUP BY conversations.id
ORDER BY messages.message_id DESC
Upvotes: 1
Views: 320
Reputation: 39
I was able to solve the problem with this query
SELECT conversations.*,m.message FROM `conversations` LEFT JOIN messages as m on conversations.id = m.conversation_id AND m.id = (SELECT MAX(ms.id) from messages as ms where ms.conversation_id = conversations.id)
I left join the messages and when doing so i select the top most message. Order by can be used to give the order by date
Upvotes: 2
Reputation: 1271241
You want the maximum message for the conversation not the therapist, so I suspect you want this in this subquery:
AND messages.message_id =
(
SELECT MAX(message_id)
FROM messages z
WHERE z.message_id = conversations.id
)
I'm not sure the outer GROUP BY
is correct (it may not be needed at all and it seems to conflict with the columns of the SELECT
), but without sample data and desired results, it is hard to tell.
EDIT:
I see, the problem is the filtering on therapist id in the outer query. That makes this a bit more complicated:
AND messages.message_id =
(
SELECT MAX(message_id)
FROM messages z JOIN
conversations c
ON m.conversation_id = c.id
WHERE c.therapist_id_fk = 1 AND -- restriction on subquery
z.message_id = conversations.id -- correlation to outer query
)
Don't use single quotes for constants unless the column is a string or date.
Upvotes: 3
Reputation: 40730
Your problem is the bit:
SELECT MAX(message_id)
FROM messages z
WHERE z.therapist_id_fk = conversations.therapist_id_fk
This query only matches exactly one message. You'd need something a bit different. Try this:
SELECT
conversations.*,
messages.message,
patients.first_name as fullname,
patients.city,
patients.thumb,
patients.gender,
patients.online_status
FROM
patients
INNER JOIN conversations
ON conversations.patient_id_fk = patients.id
LEFT JOIN messages
ON messages.conversation_id = conversations.id
WHERE conversations.therapist_id_fk='1'
AND messages.message_id IN ( -- new bit
SELECT MAX(message_id) FROM messages z GROUP BY z.conversation_id
)
GROUP BY conversations.id
ORDER BY messages.message_id DESC
Upvotes: 1
Reputation: 151
SELECT
conversations.*,
messages.message,
patients.first_name as fullname,
patients.city,
patients.thumb,
patients.gender,
patients.online_status
FROM
patients
INNER JOIN conversations
ON conversations.patient_id_fk = patients.id
LEFT JOIN messages
ON messages.conversation_id = conversations.id
AND messages.message_id =
(
SELECT MAX(message_id)
FROM messages z
WHERE z.conversation_id = conversations.id
)
WHERE conversations.therapist_id_fk='1'
ORDER BY messages.message_id DESC
Found the issue. It was here:
AND messages.message_id = ( SELECT MAX(message_id) FROM messages z WHERE z.conversation_id = conversations.id )
I was using this:
AND messages.message_id = ( SELECT MAX(message_id) FROM messages z WHERE z.therapist_id_fk = conversations.therapist_id_fk )
Upvotes: 2