MySQL query with count and row

Question

I have a messages table and, I would like to know what would be the most efficient query to accomplish the following result:

Note thread field is null if the thread is the very first message all other messages are linked to that thread with is the emid

CREATE TABLE `messages` (
    `emid` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `emitter` BIGINT(20) NOT NULL,
    `receiver` BIGINT(20) NOT NULL,
    `thread` BIGINT(20) DEFAULT NULL,
    `opened` TINYINT(4) DEFAULT 0,
    `message` BLOB NOT NULL,
    `timecard` DATETIME DEFAULT CURRENT_TIMESTAMP,
    ADD CONSTRAINT `efk` FOREIGN KEY (`emitter`) REFERENCES `members` (`user_id`),
    ADD CONSTRAINT `rfk` FOREIGN KEY (`receiver`) REFERENCES `members` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I would like to get the first row for a given receiver including its messages count. Say for instance a user has 4 non opened messages (unread) and two replies. I would like to get the first message received under this thread and the total replies from both sides. At first I thought about sub queries but that seems like it will perform badly.

The following selects threads for user (receiver = id)

    SELECT * FROM `messages` WHERE thread IS NULL AND receiver = 2 ORDER BY timecard DESC

This one get the message count under a given thread

        SELECT COUNT(*) FROM `messages` WHERE thread = 20

Getting the first message in the thread requires a subquery, see stackoverflow.com/questions/7745609/…. You can simply add COUNT(*) to the subquery that returns MIN(timecard) for each thread. — Barmar
– Barmar, Commented Feb 13, 2018 at 22:15
Please try to do that, and if you can't get it to work post your attempt. Then we can help you fix it. We're not going to write it for you. — Barmar
– Barmar, Commented Feb 13, 2018 at 22:16
I can write no problem, and not asking to do it for me. My goal is to learn not to have done. — Gacci
– Gacci, Commented Feb 13, 2018 at 22:17
@Barmar There it is! What I would like is to know if there is a better way to accomplish that! Thread count and message count for each thread — Gacci
– Gacci, Commented Feb 13, 2018 at 22:22
What does that have to do with the question? Show how you try to join the queries to produce the result you want. — Barmar
– Barmar, Commented Feb 13, 2018 at 22:24

Barmar · Accepted Answer · 2018-02-13 22:50:48Z

1

Join your two queries:

SELECT m1.*, IFNULL(COUNT(m2.emid), 0) AS replies
FROM messages AS m1
LEFT JOIN messages AS m2 ON m2.thread = m1.emid
WHERE m1.thread is NULL
GROUP BY m1.emid

The WHERE clause selects just the starting message from each thread from table m1.

LEFT JOIN then relates that to all the replies to that thread, using the thread column to link them back to the original message. I've used LEFT JOIN so that messages with no replies will be included.

COUNT(m2.emid) then counts the number of related rows, which is the number of replies in the thread. COUNT() only counts non-null values; if there are no replies, LEFT JOIN sets this column to NULL, so you'll get a count of 0.

edited Feb 13, 2018 at 22:50

answered Feb 13, 2018 at 22:36

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Barmar Over a year ago

@RaymondNijland No it won't. All the other columns are functionally dependent on emid, since it's the primary key, so they don't need to be listed in GROUP BY.

Barmar Over a year ago

Assuming he's running 5.7.5 or newer, which is when ONLY_FULL_GROUP_BY became the default.

Gacci Over a year ago

Would you mind explaining the query a little bit, as I mentioned before my goal is to learn!

Raymond Nijland Over a year ago

@Barmar some MySQL versions didn't implement the GROUP BY on a PRIMARY KEY with sql_mode = ONLY_FULL_GROUP_BY correctly that's why i don't rely on it.

Gacci Over a year ago

I mean to ask you if any of you could explain the full query!

|

Collectives™ on Stack Overflow

MySQL query with count and row

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related