除了左连接和联合mysql查询之外,还从两个表中选择最新的条目
I have three tables that record conversation activity and one table containing user info
I am trying to put a query together that summarises a users conversations (like an inbox with the last comment visible)
my tables are set up like the below
users
---
company | contact_person | pic_small
alerts
---
comment_id | user_id | poster_id | timestamp
activity
---
comment_id | user_id | comment | timestamp
comments
---
comment_id | user_id | comment | timestamp
When a user initially makes contact, the timestamp, comment_id and id's of the users (person sending and person receiving) get inserted into the alerts table. The user_id, comment_id, timestamp and actual comment also get inserted into the activity table.
Then once the conversation has started, all comments (comment_id, user_id,comment, timestamp) get inserted into the comments table.
As mentioned above, what I am trying to do is summarise a users activity so it looks like their inbox.
I have come up with the below query that gives me the user id's, comment id's and user details of all the conversation activity received or sent by logged in user.
SELECT alerts.comment_id,
alerts.user_id,
alerts.poster_id,
alerts.timestamp,
users.contact_person,
users.company,
users.pic_small
FROM alerts
LEFT JOIN users ON users.user_id = alerts.user_id
WHERE alerts.user_id = %s
GROUP BY alerts.comment_id
UNION
SELECT alerts.comment_id,
alerts.user_id,
alerts.poster_id,
alerts.timestamp,
users.contact_person,
users.company,
users.pic_small
FROM alerts
LEFT JOIN users ON users.user_id = alerts.poster_id
WHERE alerts.user_id = %s
GROUP BY alerts.comment_id
ORDER BY TIMESTAMP DESC
The part I am stuck on is getting the last comment (comment with newest timestamp) from either the activity or comments table (It could be either). Happy to change the above query completely if needed.
Below is what I am trying to achieve, I only want to see the user details of people that have contacted me or who I have contacted - not my own details in my inbox (I can do this with php if needed) - It doesn't matter if the last comment was mine however.
Still it is not clear to me why you are using union.
You can get latest comment by below query, if you want to join some other results of other user_id then you can union these results in same way or let me know if you need different results.
SELECT
comment_id,comment, user_id,poster_id,TIMESTAMP, contact_person,company,pic_small
FROM
(SELECT
alerts.comment_id,alerts.user_id,alerts.poster_id,alerts.timestamp,users.contact_person,users.company,users.pic_small,act.comment
FROM alerts
JOIN activity act
ON act.comment_id=alerts.comment_id
LEFT JOIN users ON users.user_id = alerts.user_id
WHERE alerts.user_id = %s
ORDER BY act.timestamp DESC) a
GROUP BY comment_id;