Summary: I want to Order by before Group
I find a nice article about the problem, but finally not successful to fix it. https://eddies-shop.medium.com/mysql-when-to-order-before-group-13d54d6c4ebb
My Server config:
About the query: I get a list of rooms and messages, but I only need the latest message for each room.
so I need to group by conversation_id, and sort by message_id or message_time.
The above query works well but is not complete. In that, for each room, we have duplicate rows.
When I try and uncomment the last line from the query
And when I try to apply GROUP BY main.conversation_id. It is no longer in order and the order is broken again.
My Query:
SELECT
main.*
FROM
(
SELECT
sub.*
FROM
(
SELECT
conversation.id AS conversation_id,
CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
conversation.is_group AS conversation_isgroup,
(SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,
message.id AS message_id,
message.type AS message_type,
message.body AS message_body,
message.filename AS message_filename,
message.created_at AS message_time,
message.user_id AS message_user_id,
CONCAT(user.first_name, " ", user.last_name) AS message_user_name
FROM
conversation
INNER JOIN
conversation_member
ON
conversation_member.conversation_id = conversation.id
LEFT JOIN
message
ON
message.conversation_id = conversation.id
LEFT JOIN
user
ON
user.id = message.user_id
LEFT JOIN
user as user2
ON
user2.id = conversation.owner2_id
OR
user2.id = conversation.owner_id
WHERE
user2.id != 1
AND
name IS NULL
AND
conversation_member.user_id = 1
AND
conversation.is_group = 0
) AS sub
ORDER BY
sub.message_id DESC
) as main
# GROUP BY
# main.conversation_id
If you need to know more about the Database structure:
--
-- Table structure for table `conversation`
--
CREATE TABLE `conversation` (
`id` int(50) NOT NULL,
`name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`owner_id` int(50) NOT NULL,
`owner2_id` int(50) DEFAULT NULL,
`is_group` int(2) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Dumping data for table `conversation`
--
INSERT INTO `conversation` (`id`, `name`, `owner_id`, `owner2_id`, `is_group`, `created_at`, `updated_at`) VALUES
(7, 'تالار گفتگوی ریاضی', 1, NULL, 1, '2021-09-13 20:33:38', NULL),
(8, NULL, 2, 1, 0, '2021-09-13 20:33:46', '2021-09-14 07:55:44'),
(9, 'گروه ازمایشی', 3, NULL, 1, '2021-09-14 07:45:04', NULL),
(10, 'پروژه ها و ایده ها', 3, NULL, 1, '2021-09-14 07:47:19', NULL),
(11, NULL, 4, 1, 0, '2021-09-14 08:05:11', NULL);
--
-- Table structure for table `conversation_member`
--
CREATE TABLE `conversation_member` (
`id` int(50) NOT NULL,
`conversation_id` int(50) NOT NULL,
`user_id` int(50) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Dumping data for table `conversation_member`
--
INSERT INTO `conversation_member` (`id`, `conversation_id`, `user_id`, `created_at`) VALUES
(1, 8, 1, '2021-09-14 07:02:18'),
(2, 8, 2, '2021-09-14 07:02:18'),
(3, 7, 1, '2021-09-14 07:02:28'),
(4, 7, 3, '2021-09-14 07:02:28'),
(5, 9, 3, '2021-09-14 07:45:13'),
(7, 10, 4, '2021-09-14 08:02:57'),
(8, 10, 2, '2021-09-14 08:02:57'),
(9, 10, 1, '2021-09-14 08:03:05'),
(10, 11, 4, '2021-09-14 08:05:23'),
(11, 11, 1, '2021-09-14 08:05:23'),
(12, 7, 4, '2021-09-14 09:30:04');
--
-- Table structure for table `message`
--
CREATE TABLE `message` (
`id` int(50) NOT NULL,
`conversation_id` int(50) NOT NULL,
`user_id` int(50) DEFAULT NULL,
`type` int(2) NOT NULL COMMENT '0=system,1=message,1=file,2=voice',
`body` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`filename` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_group` int(2) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`edited_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Dumping data for table `message`
--
INSERT INTO `message` (`id`, `conversation_id`, `user_id`, `type`, `body`, `filename`, `is_group`, `created_at`, `edited_at`) VALUES
(1, 7, 1, 1, '1', NULL, 1, '2021-09-14 07:16:12', '2021-09-14 14:14:44'),
(2, 8, 1, 1, '2', NULL, 0, '2021-09-14 07:16:25', '2021-09-14 14:14:45'),
(3, 11, 1, 1, '3', NULL, 0, '2021-09-14 13:21:30', '2021-09-14 14:14:47'),
(4, 10, 1, 1, '4', NULL, 1, '2021-09-14 13:23:34', '2021-09-14 14:14:49'),
(5, 7, 1, 1, '5', NULL, 1, '2021-09-14 13:25:16', '2021-09-14 14:14:51'),
(6, 7, 1, 1, '6', NULL, 1, '2021-09-14 13:30:40', '2021-09-14 14:14:52'),
(7, 7, 1, 1, '7', NULL, 1, '2021-09-14 13:49:29', '2021-09-14 14:14:54'),
(8, 7, 1, 1, '8', NULL, 1, '2021-09-14 13:49:34', '2021-09-14 14:14:56'),
(9, 10, 1, 1, '9', NULL, 1, '2021-09-14 13:54:04', '2021-09-14 14:14:57'),
(10, 7, 1, 1, '10', NULL, 1, '2021-09-14 14:01:18', '2021-09-14 14:14:59'),
(11, 8, 1, 1, '11', NULL, 0, '2021-09-14 14:07:48', '2021-09-14 14:15:03'),
(12, 11, 4, 1, 'test-new', NULL, 0, '2021-09-14 15:11:51', NULL),
(13, 11, 1, 1, 'fdgdfg', NULL, 0, '2021-09-14 13:21:30', '2021-09-14 14:14:47'),
(14, 11, 1, 1, 'sd1f23sd1f', NULL, 0, '2021-09-14 15:33:02', NULL),
(15, 11, 1, 1, 'dfgdfgdfgdfg', NULL, 0, '2021-09-14 15:33:02', NULL),
(16, 11, 1, 1, 'dfgdfgdfg', NULL, 0, '2021-09-14 15:33:06', NULL),
(17, 11, 1, 1, 'dfg345345345', NULL, 0, '2021-09-14 15:33:06', NULL),
(18, 11, 1, 1, 'gdfg234234234', NULL, 0, '2021-09-14 15:33:17', NULL),
(19, 11, 1, 1, 'dfgda1323123f', NULL, 0, '2021-09-14 15:33:17', NULL),
(20, 11, 1, 1, '234234234234', NULL, 0, '2021-09-14 15:33:17', NULL),
(21, 11, 1, 1, '345345345345', NULL, 0, '2021-09-14 15:33:17', NULL),
(22, 11, 1, 1, '5565656', NULL, 0, '2021-09-14 15:33:17', NULL),
(23, 11, 1, 1, '7787878', NULL, 0, '2021-09-14 15:33:17', NULL),
(24, 11, 1, 1, 'یبلیبلیبلیبل', NULL, 0, '2021-09-14 15:33:28', NULL),
(25, 11, 1, 1, 'ض۳۲ث۱۲۳۴۲۳۴۲۳۴', NULL, 0, '2021-09-14 15:33:28', NULL),
(26, 11, 1, 1, '۳۴۵سیبیلبیبلب', NULL, 0, '2021-09-14 15:33:28', NULL),
(27, 11, 1, 1, 'فقفثفثقفثقف', NULL, 0, '2021-09-14 15:33:28', NULL),
(28, 11, 1, 1, '۳۳۴۲۴۲۳۴', NULL, 0, '2021-09-14 15:33:28', NULL),
(29, 11, 1, 1, '$$$$', NULL, 0, '2021-09-14 15:33:28', NULL),
(30, 11, 1, 1, '$$$%%dfgdfg', NULL, 0, '2021-09-14 15:33:47', NULL),
(31, 11, 1, 1, 'dfgdfg23423423423سیبلیبل', NULL, 0, '2021-09-14 15:33:47', NULL),
(32, 11, 1, 1, 'یبلص۴۳۵۲۳۴۵۳۴۵۳۴۵', NULL, 0, '2021-09-14 15:33:47', NULL),
(33, 11, 1, 1, 'یبل۳۵۳۴۵فثقیبلیبلیبل', NULL, 0, '2021-09-14 15:33:47', NULL),
(34, 11, 1, 1, 'یبلیلبل۳۴۵۳۴۵۳۴۵۳۴۵۳۴۵', NULL, 0, '2021-09-14 15:33:47', NULL),
(35, 11, 1, 1, '$$$$$####', NULL, 0, '2021-09-14 15:33:47', NULL);
--
-- Table structure for table `message_view`
--
CREATE TABLE `message_view` (
`id` int(50) NOT NULL,
`message_id` int(50) NOT NULL,
`conversation_id` int(50) NOT NULL,
`is_group` int(2) NOT NULL,
`user_id` int(50) NOT NULL,
`viewed_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `session`
--
CREATE TABLE `session` (
`id` int(50) NOT NULL,
`user_id` int(50) NOT NULL,
`device` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`code` int(10) DEFAULT NULL,
`secret` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Dumping data for table `session`
--
INSERT INTO `session` (`id`, `user_id`, `device`, `code`, `secret`) VALUES
(1, 1, '08ad5559-15f7-4c32-ab2d-2d8a563670a3', 0, '1'),
(2, 1, 'c8216675-08ff-4deb-9341-2afbe88bc716', NULL, '41234'),
(3, 1, 'f20279f4-615d-4b6f-83e4-0e6c201395e3', 53741, NULL),
(4, 1, 'bef6f3e2-04b7-4ff5-b948-c035c376f4d2', 51003, NULL),
(5, 1, '3492d860-6ae4-4a00-a65d-346d880c4e71', 30646, NULL),
(6, 1, 'fd0b2d35-d7aa-4e40-b362-6b592dc17aad', 28576, NULL),
(7, 1, 'c69516b4-53a8-4e69-a874-a11d859b451d', 32440, NULL),
(8, 1, '075149a4-d94c-4246-99ce-d0b71c72f26c', 99800, NULL),
(9, 1, '4804b854-89b6-4c85-8df3-815ffdd34fba', 19774, NULL),
(10, 1, 'a60195ac-2e50-42ed-9d71-1d3d04729339', 10262, NULL),
(11, 1, '80a9a2e7-ee8b-47ef-8ca4-75216721a6ac', 88424, 'bc82131e-0e59-4841-98b7-798cf65d9fcb'),
(12, 1, '6c120179-312f-4d73-9488-7f692cb54234', 42832, NULL),
(13, 1, 'a8b7ca5b-a47d-48b0-afeb-197b5ec7dc44', 39034, 'fdd51fe1-5bae-424f-9515-1a1b435faed6'),
(14, 1, '0652aa2a-01df-497d-b7b2-77fc87a29c24', 36133, '8fc206e1-1a49-444b-bcb6-ea289c17a918'),
(15, 1, '6b0bf6b9-4d32-4c6b-9e84-fd0b393acb31', 87972, '05de4b7a-cc6e-4ff0-b321-72473ac903bd'),
(16, 1, '0a523464-89ab-4f4a-803e-ca252e637e4f', 40843, 'a35a2169-949d-4043-a061-1a56ec30440e'),
(17, 1, '34b2e1bf-a088-48e8-85b8-1943db001fd5', 65916, '971a3cea-8ccc-45bf-887e-7797e4c6ab22'),
(18, 1, 'fcdb6c98-d044-43e8-a373-351f7ca1536d', 82257, 'bacb442d-e066-4117-a380-c468316d47f2'),
(19, 1, '565a036d-c4f8-46b0-8493-e5371e3dccca', 71626, '610b4e81-cf72-4091-a711-d64c601e0f0c'),
(20, 1, '8775a2d9-4544-48c4-ab5c-6d7216c955f7', 46912, 'b5eabb83-e372-4e7b-a48a-3c99eaba5d6d'),
(21, 1, 'a94f734b-0aa0-4ee9-aea3-86e2a405cf56', 44508, '2db51630-e32a-4172-90aa-99d5f7b00063'),
(22, 1, 'b0ed316b-483b-47cc-b27f-2fe6b83f410a', 87850, 'f68377a4-3ed8-4c36-8a78-1d807ed50449'),
(23, 1, '08f38458-cd12-4a9c-9c63-9c6aa291956b', 79266, 'e6314070-8401-4d7b-b9d7-a44c147c75ec'),
(24, 4, '192dfd3f-b2d7-401f-bf95-2d663b6badab', 65088, '6fa66aa9-f47d-46fd-9ce5-9645802383da'),
(25, 4, 'fc8fe94b-f220-42db-ab77-74de994f8275', 37715, '0e188502-67c2-44a9-b68c-32b07fa150ab'),
(26, 1, '27451de6-c730-450a-b76c-3ea53ff74580', 16934, '662ae741-fa42-4fc3-b0bb-5ae6c9e67b52'),
(27, 4, '6ba92f55-cffe-4d9e-b646-be9cf07e99e3', 91683, '27b85d09-831d-41cd-8032-17743a76616d');
--
-- Table structure for table `user`
--
CREATE TABLE `user` (
`id` int(50) NOT NULL,
`first_name` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_name` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`country_code` int(2) NOT NULL,
`phone_number` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Dumping data for table `user`
--
INSERT INTO `user` (`id`, `first_name`, `last_name`, `country_code`, `phone_number`, `created_at`, `updated_at`) VALUES
(1, 'Max', 'Base', 98, '9134458080', '2021-09-13 18:28:47', '2021-09-14 07:13:21'),
(2, 'Ali', 'Tahmasebi', 98, '91032545254', '2021-09-14 07:02:46', '2021-09-14 08:02:24'),
(3, 'B.', 'KheirKhah', 98, '9124554020', '2021-09-14 07:02:46', '2021-09-14 08:03:20'),
(4, 'H.', 'Malekian', 98, '9134550773', '2021-09-14 07:25:21', '2021-09-14 08:02:35');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `conversation`
--
ALTER TABLE `conversation`
ADD PRIMARY KEY (`id`),
ADD KEY `owner_id` (`owner_id`),
ADD KEY `is_group` (`is_group`);
--
-- Indexes for table `conversation_member`
--
ALTER TABLE `conversation_member`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `message`
--
ALTER TABLE `message`
ADD PRIMARY KEY (`id`),
ADD KEY `is_group` (`is_group`),
ADD KEY `user_id` (`user_id`),
ADD KEY `conversation_id` (`conversation_id`);
--
-- Indexes for table `message_view`
--
ALTER TABLE `message_view`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `session`
--
ALTER TABLE `session`
ADD PRIMARY KEY (`id`),
ADD KEY `user_id` (`user_id`),
ADD KEY `device` (`device`);
--
-- Indexes for table `user`
--
ALTER TABLE `user`
ADD PRIMARY KEY (`id`),
ADD KEY `phone_number` (`phone_number`),
ADD KEY `country_code` (`country_code`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `conversation`
--
ALTER TABLE `conversation`
MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
--
-- AUTO_INCREMENT for table `conversation_member`
--
ALTER TABLE `conversation_member`
MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
--
-- AUTO_INCREMENT for table `message`
--
ALTER TABLE `message`
MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
--
-- AUTO_INCREMENT for table `message_view`
--
ALTER TABLE `message_view`
MODIFY `id` int(50) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `session`
--
ALTER TABLE `session`
MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;
--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;
A bit more about the database and tables:
Purpose of conversation, conversation_member table: I have a database to store messenger data. there are 2 model conversations:
owner2_id column.)And the main query is this to merge personal chat and group chats of user who ID = 1:
(
SELECT
sub1.*
FROM
(
SELECT
conversation.id AS conversation_id,
conversation.name AS conversation_name,
conversation.is_group AS conversation_isgroup,
conversation.owner_id AS conversation_owner_id,
message.id AS message_id,
message.type AS message_type,
message.body AS message_body,
message.filename AS message_filename,
message.created_at AS message_time,
message.user_id AS message_user_id,
CONCAT(user.first_name, " ", user.last_name) AS message_user_name
FROM
conversation
INNER JOIN
conversation_member
ON
conversation_member.conversation_id = conversation.id
LEFT JOIN
message
ON
message.conversation_id = conversation.id
LEFT JOIN
user
ON
user.id = message.user_id
WHERE
conversation_member.user_id = 1
AND
conversation.is_group = 1
) AS sub1
GROUP BY sub1.message_id desc
)
UNION
(
SELECT
sub2.*
FROM
(
SELECT
conversation.id AS conversation_id,
CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
conversation.is_group AS conversation_isgroup,
(SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,
message.id AS message_id,
message.type AS message_type,
message.body AS message_body,
message.filename AS message_filename,
message.created_at AS message_time,
message.user_id AS message_user_id,
CONCAT(user.first_name, " ", user.last_name) AS message_user_name
FROM
conversation
INNER JOIN
conversation_member
ON
conversation_member.conversation_id = conversation.id
LEFT JOIN
message
ON
message.conversation_id = conversation.id
LEFT JOIN
user
ON
user.id = message.user_id
LEFT JOIN
user as user2
ON
user2.id = conversation.owner2_id
OR
user2.id = conversation.owner_id
WHERE
user2.id != 1
AND
name IS NULL
AND
conversation_member.user_id = 1
AND
conversation.is_group = 0
) AS sub2
GROUP BY sub2.message_id desc
);
I want a list of all group conversations and personal conversations and the last messages in that room and sort all of the rooms by last MESSAGE_TIME.
This is what happens to almost all messengers.
I am not a master in SQL, by the way, I just success to fix the problem of one query after hours!
If you have an any better queries for this purpose, please post and answer.
A single query:
SELECT
main.*
FROM
(
SELECT
sub.*
FROM
(
SELECT
conversation.id AS conversation_id,
CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
conversation.is_group AS conversation_isgroup,
(SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,
message.id AS message_id,
message.type AS message_type,
message.body AS message_body,
message.filename AS message_filename,
message.created_at AS message_time,
message.user_id AS message_user_id,
CONCAT(user.first_name, " ", user.last_name) AS message_user_name
FROM
conversation
INNER JOIN
conversation_member
ON
conversation_member.conversation_id = conversation.id
LEFT JOIN
message
ON
message.conversation_id = conversation.id
LEFT JOIN
user
ON
user.id = message.user_id
LEFT JOIN
user as user2
ON
user2.id = conversation.owner2_id
OR
user2.id = conversation.owner_id
WHERE
user2.id != 1
AND
name IS NULL
AND
conversation_member.user_id = 1
AND
conversation.is_group = 0
) AS sub
GROUP BY sub.message_id desc
) as main
GROUP BY main.conversation_id
Main query and UNION to merge the results:
(
SELECT
mm.*
FROM
(
(
SELECT
sub1.*
FROM
(
SELECT
conversation.id AS conversation_id,
conversation.name AS conversation_name,
conversation.is_group AS conversation_isgroup,
conversation.owner_id AS conversation_owner_id,
message.id AS message_id,
message.type AS message_type,
message.body AS message_body,
message.filename AS message_filename,
message.created_at AS message_time,
message.user_id AS message_user_id,
CONCAT(user.first_name, " ", user.last_name) AS message_user_name
FROM
conversation
INNER JOIN
conversation_member
ON
conversation_member.conversation_id = conversation.id
LEFT JOIN
message
ON
message.conversation_id = conversation.id
LEFT JOIN
user
ON
user.id = message.user_id
WHERE
conversation_member.user_id = 1
AND
conversation.is_group = 1
) AS sub1
GROUP BY sub1.message_id desc
)
UNION
(
SELECT
sub2.*
FROM
(
SELECT
conversation.id AS conversation_id,
CONCAT(user2.first_name, " ", user2.last_name) AS conversation_name,
conversation.is_group AS conversation_isgroup,
(SELECT user_id FROM conversation_member WHERE conversation_id = conversation.id AND user_id != 1) AS conversation_owner_id,
message.id AS message_id,
message.type AS message_type,
message.body AS message_body,
message.filename AS message_filename,
message.created_at AS message_time,
message.user_id AS message_user_id,
CONCAT(user.first_name, " ", user.last_name) AS message_user_name
FROM
conversation
INNER JOIN
conversation_member
ON
conversation_member.conversation_id = conversation.id
LEFT JOIN
message
ON
message.conversation_id = conversation.id
LEFT JOIN
user
ON
user.id = message.user_id
LEFT JOIN
user as user2
ON
user2.id = conversation.owner2_id
OR
user2.id = conversation.owner_id
WHERE
user2.id != 1
AND
name IS NULL
AND
conversation_member.user_id = 1
AND
conversation.is_group = 0
) AS sub2
GROUP BY sub2.message_id desc
)
) AS mm
GROUP BY mm.conversation_id desc
)
;
I am not sure the query is optimized or good. but this works. please review and comment on my query if this is not good enough.