Hkm Sadek
Reputation: 3209
Mysql complex select statement
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;
Upvotes: 1
Views: 129
Answers (2)
sticky bit
Reputation: 37507
It can basically be done with some joins. The tricky part is the last message for chats. The subquery groups the chats by the con_id to get the maximum id. As id is AUTO_INCREMENT, the maximum id for a con_id is the last message of the conversation.
SELECT co1.id,
co1.sender,
co1.reciever,
us.id "id(users id)",
us.username,
ch1.id "id(chats id)",
ch1.con_id,
ch1.msg,
ch1.msg_sender
FROM conversations co1
INNER JOIN users us
ON CASE
WHEN co1.sender <> 1
THEN co1.sender
WHEN co1.reciever <> 1
THEN co1.reciever
END = us.id
INNER JOIN chats ch1
ON ch1.con_id = co1.id
INNER JOIN (SELECT max(ch2.id) id,
ch2.con_id
FROM chats ch2
GROUP BY ch2.con_id) ch3
ON ch3.con_id = ch1.con_id
AND ch3.id = ch1.id
WHERE 1 IN (co1.sender,
co1.reciever);
Instead of an INNER JOIN with the subquery with the GROUP BY, a correlated subyquery would be another option.
SELECT co1.id,
co1.sender,
co1.reciever,
us.id "id(users id)",
us.username,
ch1.id "id(chats id)",
ch1.con_id,
ch1.msg,
ch1.msg_sender
FROM conversations co1
INNER JOIN users us
ON CASE
WHEN co1.sender <> 1
THEN co1.sender
WHEN co1.reciever <> 1
THEN co1.reciever
END = us.id
INNER JOIN chats ch1
ON ch1.con_id = co1.id
WHERE 1 IN (co1.sender,
co1.reciever)
AND ch1.id = (SELECT max(ch2.id)
FROM chats ch2
WHERE ch2.con_id = co1.id);
Upvotes: 1
artmasterpl
Reputation: 11
TRy it now
SELECT id.conversations, sender.conversations, receiver.conversations, id.users, userName.users, id.chats, con_id.chats, msg.chats, msg_sender.chats
FROM conversations, chats, users
WHERE (sender.conversations OR receiver.conversations = 1) AND id.conversations=firstname.users
GROUP BY id.conversations
Upvotes: 0