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 https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1. You can simply add `COUNT(*)` to the subquery that returns `MIN(timecard)` for each thread. — Barmar, Feb 13 '18 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, Feb 13 '18 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, Feb 13 '18 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, Feb 13 '18 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, Feb 13 '18 at 22:24
Neither of those queries looks like anything in the question I referred you to, to get the first message of each thread. — Barmar, Feb 13 '18 at 22:25
I guess because that does not resemble what I am looking for. It does work for me separately. I just wanna know if there is a way to do it more efficiently than with a sub query! — Gacci, Feb 13 '18 at 22:32
I misunderstood, I thought the first message was the lowest `timecard`. — Barmar, Feb 13 '18 at 22:33

Barmar · Answer 1 · 2018-02-13T22:50:48.993

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 '18 at 22:50

answered Feb 13 '18 at 22:36

Barmar

741,623
53
500
612

@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 Feb 13 '18 at 22:42
Assuming he's running 5.7.5 or newer, which is when ONLY_FULL_GROUP_BY became the default. – Barmar Feb 13 '18 at 22:42
Would you mind explaining the query a little bit, as I mentioned before my goal is to learn! – Gacci Feb 13 '18 at 22:43
@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. – Raymond Nijland Feb 13 '18 at 22:44
I mean to ask you if any of you could explain the full query! – Gacci Feb 13 '18 at 22:49
I meant more explaining on the Aliasing I am pretty familiar with the very basics. The reason why I would like some explaining is because I at first glance I would think the query will go into an infinite loop. Also, seems to me like the aliasing prevents that from happening! – Gacci Feb 13 '18 at 22:53
We're getting into some pretty basic SQL concepts here. SO is not a tutoring service, or a replacement for learning the language. The aliases allow you to refer to the same table twice, as if they were copies. – Barmar Feb 14 '18 at 19:15
Is there a reason why removed your answer? – Gacci Feb 14 '18 at 23:17
You mean in your other question? Because I misunderstood the question and the answer was wrong. – Barmar Feb 14 '18 at 23:20

MySQL query with count and row

1 Answers1