CODEWITHSUNDEEP
mysql
Gacci
Gacci

Reputation: 1398

MySQL query with count and row

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

Upvotes: 0

Views: 67

Answers (1)

Barmar
Barmar

Reputation: 780871

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.

Upvotes: 1

Related Questions