Need help with two MySql queries. Join subqueries

Question

I have 2 tables:

user: id, name message: sender_id, receiver_id, message, read_at, created_at

There are 2 results that I need to get, and I'm trying to find the best solution. I have included the queries that I use at the very end.

• I need to get a list of users, and also with each user get information about whether there are any unread messages from each user (they are as a sender, I as a recipient) and if there are any read messages between us (they send me a recipient or I send them to recipients)

• I need The same as above, but only those members where there was some kind of messaging between us, first sorted unread, and then by the last message received.

Can you advise? Should this be done using joins or subqueries?

In the first case, I don’t need an account, I just need to know if there is at least one unread message. I am sending the code and my current requests, please see when you have a chance:

By the way, everything is the way I want in the first request.

My concern: in the second request, which I would like to order from .created_at messages, but I don’t think I can do this with a grouping? And also I do not know if this approach is the most optimized and fast.

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `user` VALUES (1,'User 1'),(2,'User 2'),(3,'User 3'),(4,'User 4'),(5,'User 5');

CREATE TABLE `message` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sender_id` bigint(20) DEFAULT NULL,
  `receiver_id` bigint(20) DEFAULT NULL,
  `message` text,
  `read_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `message` VALUES (1,3,1,'Messge',NULL,'2010-10-10 10:10:10'),(2,1,4,'Hey','2010-10-10 10:10:12','2010-10-10 10:10:11'),(3,4,1,'Hello','2010-10-10 10:10:19','2010-10-10 10:10:15'),(4,1,4,'Again','2010-10-10 10:10:25','2010-10-10 10:10:21'),(5,3,1,'Hiii',NULL,'2010-10-10 10:10:21');

SELECT u.*, m_new.id as have_new, m.id as have_any
FROM user u 
LEFT JOIN message m_new ON (u.id = m_new.sender_id AND m_new.receiver_id = 1 AND m_new.read_at IS NULL)
LEFT JOIN message m ON ((u.id = m.sender_id AND m.receiver_id = 1) OR (u.id = m.receiver_id AND m.sender_id = 1))
GROUP BY u.id

SELECT u.*, m_new.id as have_new, m.id as have_any
FROM user u 
LEFT JOIN message m_new ON (u.id = m_new.sender_id AND m_new.receiver_id = 1 AND m_new.read_at IS NULL)
LEFT JOIN message m ON ((u.id = m.sender_id AND m.receiver_id = 1) OR (u.id = m.receiver_id AND m.sender_id = 1))
where m.id IS NOT NULL
GROUP BY u.id

Answer 1

, JOIN , . , , , . . №2, :

SELECT u.*,
    m_new.id AS have_new,
    MAX(m_new.created_at) AS new_created,
    m.id AS have_any,
    MAX(m.created_at) AS created
FROM USER u 
LEFT JOIN message AS m_new
    ON u.id = m_new.sender_id
    AND m_new.receiver_id = 1
    AND m_new.read_at IS NULL
LEFT JOIN message AS m
    ON (u.id = m.sender_id AND m.receiver_id = 1)
    OR (u.id = m.receiver_id AND m.sender_id = 1)
WHERE m.id IS NOT NULL
GROUP BY u.id
ORDER BY new_created DESC,
    created DESC
;

---

SO JOIN vs :

• SQL:

Answer 2

:

SELECT  u.*,
        EXISTS
        (
        SELECT  NULL
        FROM    message
        WHERE   sender_id = u.id
                AND receiver_id = 1
                AND read_at IS NULL
        ) have_new,
        EXISTS
        (
        SELECT  NULL
        FROM    message
        WHERE   (
                sender_id = u.id AND receiver_id = 1
                OR sender_id = 1 AND receiver_id = u.id
                )
                AND read_at IS NOT NULL
        ) have_any
FROM    user u

:

SELECT  u.*,
        EXISTS
        (
        SELECT  NULL
        FROM    message
        WHERE   sender_id = u.id
                AND receiver_id = 1
                AND read_at IS NULL
        ) have_new,
        (
        SELECT  created_at
        FROM    message
        WHERE   (
                sender_id = u.id AND receiver_id = 1
                OR sender_id = 1 AND receiver_id = u.id
                )
        ORDER BY
                created_at DESC
        LIMIT 1
        ) last_message
FROM    (
        SELECT  DISTINCT sender_id AS party
        FROM    message
        WHERE   receiver_id = 1
        UNION
        SELECT  DISTINCT receiver_id
        FROM    message
        WHERE   sender_id = 1
        ) m
JOIN    user u
ON      u.id = m.party
ORDER BY
        have_new DESC, last_message DESC

:

messages (sender_id, receiver_id, last_message)
messages (receiver_id, sender_id, last_message)