CODEWITHSUNDEEP
mysqlsqlself-joinlaravel-query-builder
murad
murad

Reputation: 195

Mysql self joining on max id returned from first table using groupby

I need a sql that uses self join on max id from first table. Please look at the following image of table. I am grouping the table by service_id but I need the last message of each group. So For service group 5 message count should be 3 and last_message should be thirdMsg5. I wrote a sql below everything else is fine but it is throwing an error in the case of self join. It can't recognize msgTbl1.last_message_id. I think I am calling it before preparing it. I need help to solve this problem what would be the best sql to solve this in one query? And if possible please provide me this query in laravel query builder format.

SELECT count(msgTbl1.id) as message_count, 
       max(msgTbl1.id) as last_message_id, 
       msgTbl1.body, 
       msgTbl2.body as last_message, 
       services.name as service_name
FROM messages msgTbl1
LEFT JOIN (SELECT id, body FROM messages) AS msgTbl2 
   ON msgTbl2.id = msgTbl1.last_message_id
LEFT JOIN services on services.id = msgTbl1.service_id 
WHERE receiver_id = 4 AND read_user = 'no'
GROUP BY msgTbl1.service_id

enter image description here

sql for the message table

CREATE TABLE `messages` (
  `id` int(11) UNSIGNED NOT NULL,
  `sender_id` int(11) UNSIGNED DEFAULT NULL,
  `receiver_id` int(11) UNSIGNED DEFAULT NULL,
  `service_id` int(11) UNSIGNED NOT NULL,
  `sender_type` enum('user','agent','admin') NOT NULL,
  `receiver_type` enum('user','agent','admin') NOT NULL,
  `body` text,
  `files` varchar(500) DEFAULT NULL COMMENT 'serialize',
  `new_notification` enum('no','yes') NOT NULL DEFAULT 'yes',
  `read_user` enum('yes','no') NOT NULL DEFAULT 'no',
  `read_agent` enum('yes','no') NOT NULL DEFAULT 'no',
  `status` enum('active','archive','deleted') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `messages` (`id`, `sender_id`, `receiver_id`, `service_id`, `sender_type`, `receiver_type`, `body`, `files`, `new_notification`, `read_user`, `read_agent`, `status`, `created_at`, `updated_at`) VALUES
(1, 22, 4, 5, 'user', 'agent', 'firstMsg5', NULL, 'yes', 'no', 'yes', 'active', '2016-03-24 00:00:00', '2016-04-12 05:40:28'),
(2, 22, 4, 5, 'user', 'agent', 'secondMsg5', NULL, 'yes', 'no', 'yes', 'active', '2016-03-24 00:00:00', '2016-04-12 05:40:31'),
(3, 22, 4, 9, 'user', 'agent', 'firstMsg9', NULL, 'yes', 'yes', 'yes', 'active', '2016-03-24 00:00:00', '2016-04-12 05:40:45'),
(4, 4, 4, 9, 'agent', 'user', 'secondMsg9', NULL, 'yes', 'yes', 'yes', 'active', '2016-03-24 00:00:00', '2016-04-12 05:40:56'),
(5, 22, 4, 5, 'user', 'agent', 'thirdMsg5', NULL, 'yes', 'yes', 'yes', 'active', '2016-03-24 00:00:00', '2016-04-12 05:41:08');

Upvotes: 1

Views: 1309

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT message_count, 
       last_message_id, 
       msgTbl1.body,        
       services.name as service_name
FROM messages msgTbl1
INNER JOIN (
   SELECT MAX(id) AS last_message_id, COUNT(*) AS message_count
   FROM messages
   WHERE read_user = 'no'
   GROUP BY service_id) AS msgTbl2 
   ON msgTbl1.id = msgTbl2.last_message_id
LEFT JOIN services on services.id = msgTbl1.service_id 
WHERE receiver_id = 4

Upvotes: 1

Related Questions