I have a case which seems very complex to me and I am currently doing it in 3 queries and with lots of application logic. I thought, it may be possible with only one single queries.
I have a three table,
1. conversations table
2. chats table
3. users table
Please see the two pictures so that you can see the entities with the actual data.
conversations table
chats table
Please look at the conversation table. Here I want to get all the rows where sender=1 or reciever=1
so expected results will be
id 3,4,5
. Now, I also return the user details from these selected rows.
Here is the most difficult part for me. I don't want to retrieve users details of id 1
instead I want users details whose id are 2,3 and 4 notice 4 in sender column and 2 3 in reciever column
Now, you know the selected rows from conversations table. In second table, con_id is the foreign key of conversation table
so I want to retrieve the last rows of each con_id
. In the second picture, you can see id 2,3,4,5,6
has con_id = 3
but since I want the last one so it should select where id = 6
similarly all last row of each con_id
I am sorry for this long case, hope you got me and the problem.
Thanks in advance.
EDIT Here is the sql tables and dummy data for you so that if you want you can test paste quickly
Expected results
id || sender || reciver || id(users id) || userName || id(chats id) || con_id || msg || msg_sender
3 1 2 2 iamsadek2 6 3 ... 2
4 1 3 3 sadek3 10 4 ... 3
5 4 1 4 adek4. 14. 5. ... 4
DROP TABLE IF EXISTS `chats`;
CREATE TABLE `chats` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`msg_sender` int(10) unsigned DEFAULT NULL,
`con_id` int(10) unsigned DEFAULT NULL,
`msg` text,
`file` varchar(255) DEFAULT NULL,
`deleted` int(10) unsigned DEFAULT NULL,
`seen` tinyint(1) DEFAULT '0',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `con_id` (`con_id`),
KEY `msg_sender` (`msg_sender`),
CONSTRAINT `chats_ibfk_1` FOREIGN KEY (`con_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE,
CONSTRAINT `chats_ibfk_2` FOREIGN KEY (`msg_sender`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `chats` WRITE;
/*!40000 ALTER TABLE `chats` DISABLE KEYS */;
INSERT INTO `chats` (`id`, `msg_sender`, `con_id`, `msg`, `file`, `deleted`, `seen`, `created_at`, `updated_at`)
VALUES
(2,1,3,'id 1 sending msg to id 2',NULL,NULL,0,'2018-06-26 19:36:06',NULL),
(4,2,3,'id 2 sending msg to id 1 second msg',NULL,NULL,0,'2018-06-26 19:36:37',NULL),
(5,1,3,'id 1 sending msg to id 2 msg 3',NULL,NULL,0,'2018-06-26 19:36:42',NULL),
(6,2,3,'id 2 sending msg to id 1 msg 4',NULL,NULL,0,'2018-06-26 19:36:46',NULL),
(7,1,4,'id 1 sending msg to id 3 msg 1',NULL,NULL,0,'2018-06-26 19:36:49',NULL),
(8,3,4,'id 3 sending msg to id 1 msg 2am',NULL,NULL,0,'2018-06-26 19:39:44',NULL),
(9,3,4,'id 3 sending msg to id 1 msg 3',NULL,NULL,0,'2018-06-26 19:39:55',NULL),
(10,3,4,'id 3 sending msg to id 1 msg 4',NULL,NULL,0,'2018-06-26 19:39:57',NULL),
(11,4,5,'id 4 sending msg to id 1 msg 1',NULL,NULL,0,'2018-06-26 19:40:46',NULL),
(12,4,5,'id 4 sending msg to id 1 msg 1',NULL,NULL,0,'2018-06-26 19:40:48',NULL),
(13,4,5,'id 4 sending msg to id 1 msg 3',NULL,NULL,0,'2018-06-26 19:40:50',NULL),
(14,1,5,'id 1 sending msg to id 4 msg 4',NULL,NULL,0,'2018-06-26 19:41:01',NULL),
(15,4,11,'id 4 sending msg to id 3 msg 1',NULL,NULL,0,'2018-06-26 19:42:37',NULL),
(16,3,11,'id 3 sending msg to id 4 msg 2',NULL,NULL,0,'2018-06-26 19:42:57',NULL),
(17,3,11,'id 3 sending msg to id 4 msg 2',NULL,NULL,0,'2018-06-26 19:42:59',NULL);
/*!40000 ALTER TABLE `chats` ENABLE KEYS */;
UNLOCK TABLES;
# Dump of table conversations
# ------------------------------------------------------------
DROP TABLE IF EXISTS `conversations`;
CREATE TABLE `conversations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sender` int(10) unsigned DEFAULT NULL,
`reciever` int(10) unsigned DEFAULT NULL,
`status` tinyint(1) DEFAULT '0',
`type` tinyint(1) DEFAULT '0',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `sender` (`sender`),
KEY `reciever` (`reciever`),
CONSTRAINT `conversations_ibfk_1` FOREIGN KEY (`sender`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT `conversations_ibfk_2` FOREIGN KEY (`reciever`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT `conversations_reciever_foreign` FOREIGN KEY (`reciever`) REFERENCES `users` (`id`),
CONSTRAINT `conversations_sender_foreign` FOREIGN KEY (`sender`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `conversations` WRITE;
/*!40000 ALTER TABLE `conversations` DISABLE KEYS */;
INSERT INTO `conversations` (`id`, `sender`, `reciever`, `status`, `type`, `created_at`, `updated_at`)
VALUES
(3,1,2,0,0,'2018-06-26 19:32:35',NULL),
(4,1,3,0,0,'2018-06-26 19:32:50',NULL),
(5,4,1,0,0,'2018-06-26 19:33:11',NULL),
(6,2,3,0,0,'2018-06-26 19:33:22',NULL),
(11,3,4,0,0,'2018-06-26 19:33:22',NULL);
/*!40000 ALTER TABLE `conversations` ENABLE KEYS */;
UNLOCK TABLES;
# Dump of table users
# ------------------------------------------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`firstName` varchar(255) NOT NULL,
`lastName` varchar(255) NOT NULL,
`profilePic` varchar(255) DEFAULT 'user.png',
`address` varchar(255) DEFAULT NULL,
`lat` varchar(255) DEFAULT NULL,
`lang` varchar(255) DEFAULT NULL,
`ip` varchar(255) DEFAULT NULL,
`password` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`userName` varchar(255) NOT NULL,
`gender` varchar(11) DEFAULT NULL,
`userType` varchar(255) DEFAULT 'User',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`status` varchar(11) DEFAULT 'active',
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`),
UNIQUE KEY `users_username_unique` (`userName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` (`id`, `firstName`, `lastName`, `profilePic`, `address`, `lat`, `lang`, `ip`, `password`, `email`, `userName`, `gender`, `userType`, `created_at`, `updated_at`, `status`)
VALUES
(1,'sadek','hossain','user.png',NULL,NULL,NULL,NULL,'$2a$10$ayAJE7BxkgqXPGjVxeD8iu0GVWL6CXJFdLhGcHAN.i39lCqcAR5nS','[email protected]','iamsadek','male','User','2018-06-26 18:33:16','2018-06-26 18:33:16','active'),
(2,'sadek2','hossain2','user.png',NULL,NULL,NULL,NULL,'$2a$10$q.LNN48POO9g1INdEC/iTO1CJjGXNBLYZPbHkyRe.oHaZJi9b8GWe','[email protected]','iamsadek2','male','User','2018-06-26 18:42:53','2018-06-26 18:42:53','active'),
(3,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[email protected]','sadek3','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active'),
(4,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[email protected]','sadek4','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active'),
(5,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[email protected]','sadek5','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active'),
(6,'sadek3','hossain3','user.png',NULL,NULL,NULL,NULL,'$2a$10$7xgKQDUw/tA6f8zb0uqSN.z7RnNuUVAoMuB6Eknm/cqzXk7BDcmIi','[email protected]','sadek6','male','User','2018-06-26 19:02:30','2018-06-26 19:02:30','active');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
It can basically be done with some joins. The tricky part is the last message for
chats
. The subquery groups the chats by thecon_id
to get the maximumid
. Asid
isAUTO_INCREMENT
, the maximumid
for acon_id
is the last message of the conversation.SQL Fiddle
Instead of an
INNER JOIN
with the subquery with theGROUP BY
, a correlated subyquery would be another option.SQL Fiddle