Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – List out the all messages of user whether it is in sender or reciver by item id

I want the solution like each item id all messages between sender and receiver and need all rows belongs to user i.e. if sender id = 3 and receiver id = 25 then it will list out all message of between sender = 3 and receiver = 25 that belongs to item id = 15.

I tried this query but its returning other item id messages as well but I want all rows where user id = 3 messages.

SELECT c1.id, c1.sender, c1.receiver, c1.message, c1.item_id, c1.created_at
FROM chats c1
INNER JOIN (
    SELECT item_id, MAX(id) AS max_id
    FROM chats
    WHERE sender = 3 OR receiver = 3
    GROUP BY item_id
) c2 ON c1.id = c2.max_id
ORDER BY c1.created_at DESC;

Create table =>

CREATE TABLE `chats` (
  `id` bigint UNSIGNED NOT NULL,
  `sender` bigint UNSIGNED NOT NULL,
  `receiver` bigint UNSIGNED NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 => unread, 1 => read',
  `message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `item_id` bigint UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Insert table =>

INSERT INTO `chats` (`id`, `sender`, `receiver`, `is_read`, `message`, `created_at`, `updated_at`, `item_id`) VALUES
(88, 2, 1, 1, '1', '2024-04-08 13:16:44', '2024-04-08 13:17:49', 15),
(89, 2, 3, 1, '2', '2024-04-08 13:16:52', '2024-04-09 04:06:00', 3),
(90, 1, 2, 1, 'hey', '2024-04-08 13:17:56', '2024-04-08 13:18:20', 15),
(92, 2, 1, 1, 'hello', '2024-04-08 13:18:27', '2024-04-08 13:19:20', 15),
(96, 2, 3, 1, '2', '2024-04-08 13:19:26', '2024-04-09 04:06:00', 3),
(97, 1, 2, 1, 'hey', '2024-04-08 13:19:28', '2024-04-08 13:20:00', 15),
(98, 2, 3, 1, '2', '2024-04-08 13:19:56', '2024-04-09 04:06:00', 3),
(99, 2, 1, 1, '1', '2024-04-08 13:20:07', '2024-04-08 13:20:23', 15),
(100, 1, 2, 1, 'yes', '2024-04-08 13:20:34', '2024-04-08 13:20:41', 15),
(101, 2, 1, 1, 'hi', '2024-04-08 13:20:46', '2024-04-08 13:20:50', 15),
(102, 3, 2, 1, '1', '2024-04-09 04:06:07', '2024-04-09 12:07:30', 3),
(106, 1, 2, 1, 'see', '2024-04-09 11:07:03', '2024-04-09 12:06:40', 15),
(111, 2, 1, 1, '###1', '2024-04-09 12:06:32', '2024-04-09 12:11:45', 16),
(112, 2, 1, 1, '2###', '2024-04-09 12:06:48', '2024-04-09 12:11:58', 15),
(113, 2, 3, 1, 'Adam this is Adam', '2024-04-09 12:07:47', '2024-04-10 04:08:17', 3),
(114, 1, 2, 1, 'testing 1', '2024-04-09 12:11:55', '2024-04-09 12:12:13', 16),
(115, 1, 2, 1, 'testing 2', '2024-04-09 12:12:04', '2024-04-09 12:12:09', 15),
(116, 1, 2, 1, 'see merging', '2024-04-09 12:12:47', '2024-04-09 12:13:10', 15),
(117, 1, 2, 1, 'merging see', '2024-04-09 12:13:02', '2024-04-09 12:13:06', 16),
(118, 1, 2, 1, 'merging so please fix so the app is bug free', '2024-04-09 12:13:43', '2024-04-09 12:15:54', 15),
(119, 1, 2, 1, 'thanks', '2024-04-09 12:13:55', '2024-04-09 12:15:29', 16),
(120, 2, 1, 1, '#1 is here', '2024-04-09 12:15:48', '2024-04-09 12:20:25', 16),
(121, 2, 1, 1, '#2 is here', '2024-04-09 12:16:05', '2024-04-09 12:20:18', 15),
(122, 3, 2, 0, 'send me 3-4 messages', '2024-04-10 04:08:51', '2024-04-10 04:08:51', 3);

Expected table =>

Display all messages of item id = 15 for user whether he is in sender or receiver, lets say if sender is = 3 and receiver id = 25 and item id = 15 then it should display all messages for user = 3 and receiver id = 25 belongs to item id = 15 whether user = 3 in sender or receiver column and message between 3 and 25

Tags:

2

Answers


  1. 0

    The problem is that you’re selecting the maximum ID from any conversation that the user participated in, not just their messages. You need another subquery to filter to just those messages.

    WITH user_chats AS (
    SELECT *
    FROM chats
    WHERE 3 IN (sender, receiver)
)

SELECT c1.id, c1.sender, c1.receiver, c1.message, c1.item_id, c1.created_at
FROM user_chats c1
INNER JOIN (
    SELECT item_id, MAX(id) AS max_id
    FROM user_chats
    GROUP BY item_id
) c2 ON c1.id = c2.max_id
ORDER BY c1.created_at DESC;
    Login or Signup to reply.
  2. 0

    It’s not clear from your question exactly what you are trying to achieve.

    If you are logging in as user 3 and want to see all messages between yourself and user 25 about item_id 15, then:

    SELECT *
FROM chats
WHERE sender = 3 AND receiver = 25 AND item_id = 15
UNION ALL
SELECT *
FROM chats
WHERE sender = 25 AND receiver = 3 AND item_id = 15

ORDER BY created_at DESC;

    If you are logging in as user 3 and want to see the latest chat in each of your conversations related to item_id 15, then:

    WITH user_chats AS (
    SELECT MAX(id) AS max_id
    FROM chats
    WHERE 3 IN (sender, receiver)
    AND item_id = 15
    GROUP BY IF(sender = 3, receiver, sender)
)
SELECT chats.*
FROM user_chats
JOIN chats ON user_chats.max_id = chats.id
ORDER BY chats.created_at DESC;

    If you are logging in as user 3 and want to see the latest chat in each of your conversations related to all item_id, then:

    WITH user_chats AS (
    SELECT MAX(id) AS max_id
    FROM chats
    WHERE 3 IN (sender, receiver)
    GROUP BY item_id, IF(sender = 3, receiver, sender)
)
SELECT chats.*
FROM user_chats
JOIN chats ON user_chats.max_id = chats.id
ORDER BY chats.created_at DESC;

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search