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:
- Server type: MariaDB
- Server version: 10.6.4-MariaDB - Arch Linux
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:
- 1: Group: multi people
- 2: Personal chat: a user to another user (It's why I have
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
);
WHAT I'M DOING
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.
This is only for one query, I have two queries and need to UNION and merge.
A single query:
Main query and
UNION
to merge the results: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.