By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.27 |
CREATE TABLE message
(id INT, parent_id INT, sender_id INT,
receiver_id INT, content TEXT, readed INT,
sender_deleted INT, receiver_deleted INT, created DATETIME);
INSERT INTO message VALUES
(18,0,6,1,'testab',0,0,0,'2021-10-28 01:13:42'),
(19,18,6,1,'testcd',0,0,0,'2021-10-28 01:14:55')
SELECT * FROM message
WHERE (sender_id = 1 OR receiver_id = 1) AND (id = 18 OR parent_id = 18);
SELECT COUNT(id) FROM message
WHERE (sender_id = 1 OR receiver_id = 1)
AND (id = 18 OR parent_id = 18) AND (readed = 0);
| id | parent_id | sender_id | receiver_id | content | readed | sender_deleted | receiver_deleted | created |
|---|---|---|---|---|---|---|---|---|
| 18 | 0 | 6 | 1 | testab | 0 | 0 | 0 | 2021-10-28 01:13:42 |
| 19 | 18 | 6 | 1 | testcd | 0 | 0 | 0 | 2021-10-28 01:14:55 |
| COUNT(id) |
|---|
| 2 |
SELECT a.id, a.parent_id, a.content, COUNT(c.id) AS unreaded_message
FROM message a
LEFT JOIN message c ON a.id = c.id AND (a.readed = 0)
GROUP BY a.ID, a.created
ORDER BY a.id;
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_1075226673.a.parent_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SET sql_mode=''; /*setting sql_mode so this query can run*/
SELECT a.id, a.parent_id, a.content, COUNT(c.id) AS unreaded_message
FROM message a
LEFT JOIN message c ON a.id = c.id AND (a.readed = 0)
GROUP BY a.ID, a.created
ORDER BY a.id;
| id | parent_id | content | unreaded_message |
|---|---|---|---|
| 18 | 0 | testab | 1 |
| 19 | 18 | testcd | 1 |
SELECT id, parent_id, content,
SUM(CASE WHEN readed=0 THEN 1 ELSE 0 END) AS unreaded_message
FROM message
GROUP BY id, parent_id, content;
| id | parent_id | content | unreaded_message |
|---|---|---|---|
| 18 | 0 | testab | 1 |
| 19 | 18 | testcd | 1 |
INSERT INTO message VALUES
(20,18,6,1,'testde',0,0,0,'2021-10-28 01:15:05'),
(21,0,6,1,'testef',0,0,0,'2021-10-28 02:34:11'),
(22,21,6,1,'testgh',0,0,0,'2021-10-28 02:44:01');
SELECT a.id, a.parent_id, a.content, COUNT(c.id) AS unreaded_message
FROM message a
LEFT JOIN message c ON a.id = c.id AND (a.readed = 0)
GROUP BY a.ID, a.created
ORDER BY a.id;
| id | parent_id | content | unreaded_message |
|---|---|---|---|
| 18 | 0 | testab | 1 |
| 19 | 18 | testcd | 1 |
| 20 | 18 | testde | 1 |
| 21 | 0 | testef | 1 |
| 22 | 21 | testgh | 1 |
SELECT CASE WHEN parent_id=0 THEN id ELSE parent_id END AS m_id,
GROUP_CONCAT(content SEPARATOR '\r\n') AS contents,
SUM(CASE WHEN readed=0 THEN 1 ELSE 0 END) AS unreaded_message
FROM message
GROUP BY m_id;
| m_id | contents | unreaded_message |
|---|---|---|
| 18 | testab testcd testde |
3 |
| 21 | testef testgh |
2 |
UPDATE message SET readed=1 WHERE id IN (18,19);
SELECT a.id, a.parent_id, a.content, COUNT(c.id) AS unreaded_message
FROM message a
LEFT JOIN message c ON a.id = c.id AND (a.readed = 0)
GROUP BY a.ID, a.created
ORDER BY a.id;
| id | parent_id | content | unreaded_message |
|---|---|---|---|
| 18 | 0 | testab | 0 |
| 19 | 18 | testcd | 0 |
| 20 | 18 | testde | 1 |
| 21 | 0 | testef | 1 |
| 22 | 21 | testgh | 1 |
SELECT CASE WHEN parent_id=0 THEN id ELSE parent_id END AS m_id,
GROUP_CONCAT(CASE WHEN readed=0 THEN content ELSE '' END ORDER BY readed, id SEPARATOR '\r\n' ) AS contents,
SUM(CASE WHEN readed=0 THEN 1 ELSE 0 END) AS unreaded_message
FROM message
GROUP BY m_id;
| m_id | contents | unreaded_message |
|---|---|---|
| 18 | testde |
1 |
| 21 | testef testgh |
2 |