order by date from 4 different tables

column(s) for table 'messages'

poster (id of user)
post_date (date of action)

column(s) for table 'likes'

member (id of user)
date (date of action)

column(s) for table 'follows'

follower (id of user)
follow_date (date of action)

column(s) for table 'achievement_log'

member_id (id of user)
unlock_date (date of action)

I want to create a timeline with these 4 tables. The query should check these tables and retrieve the 10 latest actions of the user. In other words, rows should be ordered by the date of action all together. How can I do this?

Solution

You do this using union all and then order by and limit. Because you are looking for 10, you can limit that for each group:

(select 'message' as which, poster, post_date as date
 from messages
 where poster = @USERID
 order by post_date desc
 limit 10
) union all
(select 'likes', member, date
 from likes
 where member = @USERID
 order by date desc
 limit 10
) union all
(select 'follows', follower, follow_date
 from follows
 where follower = @USERID
 order by follow_date desc
 limit 10
) union all
(select 'achievement_log', member_id, unlock_date
 from achievement_log
 where member_id = @USERID
 order by unlock_date desc
 limit 10
)
order by date desc
limit 10;

This approach specifically uses union rather than union all, because union all is more efficient. It also does the ordering and filtering in the subqueries, so these can take advantage of indexes. For instance, an index on messages(poster, post_date) would make the first subquery more efficient.