SQL - Order by column from two tables

Question

I have two tables: messages (messages users posted), likes (many to many relationship between users and messages - it says that user1 likes message5).

messages
---------
id, id_user, message, created_at

likes
-----
id_user, id_message, created_at

If I send message, it goes to the messages table. If I like somebody's message, a new record will be created in likes table (is_user=me, id_message=message that I like).

The problem is, I would like to show history of my actions = messages and likes together in one list ordered by "created_at".

Something like:

- 1/1/2010 i sent message "aaa"
- 2/1/2010 i sent message "bbb"
- 3/1/2010 i liked somebodys's message "ccc"
- 4/1/2010 i send message "ddd"

EDIT And what's more, I want to also show details of the status I liked:

- 3/1/2010 i liked somebodys's message **"ccc"**

How to do that?

Answer 1

It would be a good idea to create a new table called 'actions' where adding a message or liking a message are recroded in this table, then it is simple to get what you want by querying this table.

If you can't do that then you use your existing schema by UNIONing the data from each table and querying the resulting table but it won't be as efficient as an index on created_at cannot be used:

SELECT * FROM
(
    SELECT 1 AS type, id, id_user, message, created_at FROM messages
    UNION ALL
    SELECT 2 AS type, NULL, id_user, id_message, created_at FROM likes
) T1
ORDER BY created_at

Add joins to get related information about the message you liked such as the username of the sender of the message and the text of the message. The task of converting this information into strings is usually best done at the application level to reduce unnecessary data transfer between your database server and your application.

If you really want to do it in the database then you can use CONCAT to build the strings:

SELECT messagetext
FROM
(
    SELECT
        created_at,
        CONCAT('I sent message "', message, '"') AS messagetext
    FROM messages
    WHERE id_user = 1
    UNION ALL
    SELECT
        likes.created_at,
        CONCAT('I liked ', messages.id_user ,'\'s message "', messages.message, '"')
    FROM likes
    JOIN messages
    ON likes.id_message = messages.id
    WHERE likes.id_user = 1
) T1
ORDER BY created_at

Result:

I sent message "aaa"
I sent message "bbb"
I liked 2's message "ccc"
I sent message "ddd"

Note that the user_id is shown instead of the user name. If you want the user name then you will also need to join to the user table (this table was not shown in your question, but I assume that you have one).

Answer 2

SELECT ....
FROM
(
    SELECT MSGS.
       ......
       MSGS.created_at
    FROM MSGS
    WHERE MSG.id_user = me
  UNION
    SELECT LIKES. 
       ..........
       LIKES.created_at
    FROM MSGS, LIKES
    WHERE MSGS.id_message = LIKES.id_message
      AND MSGS.id_user = me
  ) A
ORDER BY  A.created_at