I have two tables sms_contacts and sms_twoway_chat. Contact table use id as PK and contact_type is used for 3 psooible types of contacts (0,1 and 2)
sms_twoway_chat holds all chats between two contacts.
I need all contacts with contact_type 0, either they have chat or not
I need only contacts with contact_type 1 and 2 who has received replies
I need unread (is_read = 0) and recent response of chat (date_time desc ) on top
What I have done is showing Null on top, like contact of type 0 who don’t have any record in chat table are displaying on top, whereas they should be on last
CREATE TABLE IF NOT EXISTS sms_contacts (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) DEFAULT NULL,
contacts_name varchar(50) DEFAULT NULL,
contacts_phone varchar(11) DEFAULT NULL,
date_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
date_time_unix bigint(20) DEFAULT NULL,
contact_type int(1) DEFAULT ‘0’ COMMENT ‘0=>contact 1=>group , 2=>instant msg’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
CREATE TABLE IF NOT EXISTS sms_twoway_chat (
id int(11) NOT NULL AUTO_INCREMENT,
inbound_id varchar(50) DEFAULT NULL,
sender_id int(11) DEFAULT NULL,
receiver_id int(11) DEFAULT NULL,
is_read int(1) DEFAULT ‘0’,
msg_body text,
date_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
date_time_unix bigint(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=89 ;
Sample Data
– Dumping data for table sms_contacts
INSERT INTO sms_contacts (id, user_id, contacts_name, contacts_phone, date_time, date_time_unix, contact_type) VALUES
(1, 1, ‘Shane Higgins Cell’, ‘16198671013’, ‘2017-01-30 18:25:52’, 1485800752, 0),
(2, 1, ‘Arnetta Shepherd ssc hcm fb 1/24/17 ashepherd1966@’, ‘18572361426’, ‘2017-01-31 17:38:14’, 1485884294, 0),
(3, 1, ‘Pierrot Mervilier ssc hcm fb 1/23’, ‘13058965706’, ‘2017-01-31 17:38:43’, 1485884323, 0),
(4, 1, ‘Sean Green ssc hcm 1/22 fb’, ‘19039445724’, ‘2017-01-31 17:39:10’, 1485884350, 0),
(5, 1, ‘Antonio Rodriguez fb ssc hcm 1/20’, ‘19496624429’, ‘2017-01-31 17:39:31’, 1485884371, 0),
(6, 1, ‘Diamond Newt fb ssc 1/31 monique1027.miller@gmail.’, ‘13368655338’, ‘2017-01-31 17:44:56’, 1485884696, 0),
(7, 1, ‘Beatrice folston fb ssc hcm 1/31 beatricefolston00’, ‘16015663527’, ‘2017-01-31 17:47:24’, 1485884844, 0),
(8, 1, ‘KK Okechukwu fb ssc hcm 1/28 bensonandbentley@gmai’, ‘16788870010’, ‘2017-01-31 17:48:02’, 1485884882, 0),
(9, 1, ‘Crystal SSC RESELLER CL AD 2.1.17’, ‘16194023797’, ‘2017-02-01 04:10:28’, 1485922228, 0),
(10, 1, ‘Curtis SSC RESELLER CL AD 2.1.17’, ‘16192731911’, ‘2017-02-01 19:24:58’, 1485977098, 0),
(12, 1, ‘Keona Love ssc fb 2.1.17’, ‘18162068951’, ‘2017-02-01 20:12:05’, 1485979925, 0),
(13, 1, ‘12132466310’, ‘12132466310’, ‘2017-02-01 20:15:08’, 1485980108, 2),
(14, 1, ‘19518428547’, ‘19518428547’, ‘2017-02-01 20:15:55’, 1485980155, 2),
(15, 1, ‘test’, ‘12341231111’, ‘2017-02-03 22:15:51’, 1486160151, 0),
(16, 1, ‘test’, ‘19087770009’, ‘2017-02-03 22:16:20’, 1486160180, 0),
(17, 1, ‘16196023730’, ‘16196023730’, ‘2017-02-06 17:51:51’, 1486403511, 0),
(18, 1, ‘16195208189’, ‘16195208189’, ‘2017-02-06 17:56:34’, 1486403794, 0);
–
– Dumping data for table sms_twoway_chat
INSERT INTO sms_twoway_chat (id, inbound_id, sender_id, receiver_id, is_read, msg_body, date_time, date_time_unix) VALUES
(1, ‘0B0000002FAFA017’, 1, 1, 1, ‘Test’, ‘2017-01-30 18:25:52’, 1485800752),
(2, NULL, 1, 1, 1, ‘Received’, ‘2017-01-30 18:26:04’, 1485800764),
(3, NULL, 1, 2, 0, ‘Is this Arnetta?’, ‘2017-01-31 17:49:36’, 1485884976),
(4, NULL, 1, 3, 0, ‘Is this Pierrot?’, ‘2017-01-31 17:49:46’, 1485884986),
(5, NULL, 1, 4, 0, ‘Is this Sean?’, ‘2017-01-31 17:49:53’, 1485884993),
(6, NULL, 1, 5, 0, ‘Is this Antonio?’, ‘2017-01-31 17:50:00’, 1485885000),
(7, NULL, 1, 6, 0, ‘Is this Diamond? Or Monique?’, ‘2017-01-31 17:50:10’, 1485885010),
(8, NULL, 1, 7, 0, ‘Is this Beatrice?’, ‘2017-01-31 17:50:19’, 1485885019),
(9, NULL, 1, 7, 0, ‘Is this Benson ?’, ‘2017-01-31 17:50:35’, 1485885035),
(10, NULL, 1, 1, 1, ‘is this shane?’, ‘2017-01-31 17:50:43’, 1485885043),
(11, ‘0C00000015510F1C’, 2, 1, 1, ‘Sorry, Whose This?’, ‘2017-01-31 17:50:48’, 1485885048),
(12, ‘0C000000155115ED’, 7, 1, 1, ‘Yes’, ‘2017-01-31 17:51:13’, 1485885073),
(13, ‘0C00000015511C47’, 7, 1, 1, ‘Yes it is’, ‘2017-01-31 17:51:37’, 1485885097),
(14, NULL, 1, 2, 0, ‘Hi Arnetta, this is Shane. I own www.SmartSocialConnect.com You had entered your info from our Facebook ad so I wanted to follow up with you about it. Do you’, ‘2017-01-31 17:51:46’, 1485885106),
(15, NULL, 1, 2, 0, ‘remember? The Facebook Messenger Service for your Facebook Business Page…\n’, ‘2017-01-31 17:52:06’, 1485885126),
(16, NULL, 1, 8, 0, ‘Is this Benson?’, ‘2017-01-31 17:52:59’, 1485885179),
(17, NULL, 1, 7, 0, ‘Hi Beatrice, I’‘m Shane. I own www.SmartSocialConnect.com You had entered your info from our Facebook ad so wanted to follow up w/ you about it. Do you recall?’, ‘2017-01-31 17:53:54’, 1485885234),
(18, ‘0B0000002FF2F974’, 1, 1, 1, ‘Yes’, ‘2017-01-31 17:54:31’, 1485885271),
(19, ‘0B0000002FF2FEC6’, 7, 1, 1, ‘I think so’, ‘2017-01-31 17:54:57’, 1485885297),
(20, NULL, 1, 7, 0, 'Yea sorry for the delay in getting back to you. We are super busy, this service is a no brainer especially if you are paying to advertise on FB. ', ‘2017-01-31 17:56:01’, 1485885361),
(21, ‘0C000000155165ED’, 7, 1, 1, ‘What about’, ‘2017-01-31 17:56:04’, 1485885364),
(22, NULL, 1, 7, 0, 'Is there a good time to give you a call? Id love to explain in more detail to you about this brand new service that is guaranteed to boost your sales. ', ‘2017-01-31 17:56:42’, 1485885402),
(23, ‘0C00000015517D8E’, 7, 1, 1, ‘Tomorrow evening at 5’, ‘2017-01-31 17:57:38’, 1485885458),
(24, ‘0C0000001551808B’, 7, 1, 1, ‘I am at work now’, ‘2017-01-31 17:57:50’, 1485885470),
(25, NULL, 1, 7, 0, 'ok sounds great. I will call you then. Have a great rest of your day and sorry for bugging in the middle of your work day. ', ‘2017-01-31 17:59:23’, 1485885563),
(26, ‘0B0000002FF3405D’, 7, 1, 1, ‘That's OK be bless’, ‘2017-01-31 17:59:59’, 1485885599),
(27, ‘0B0000002FF38896’, 4, 1, 1, ‘Yes’, ‘2017-01-31 18:04:47’, 1485885887),
(28, ‘0C0000001551F6AC’, 4, 1, 1, ‘Whi this’, ‘2017-01-31 18:04:54’, 1485885894),
(29, NULL, 1, 4, 0, ‘Hi Sean I’‘m Shane I own www.SmartSocialConnect.com You had entered your info from our Facebook ad so wanted to follow up w/ you about it. Do you recall?’, ‘2017-01-31 18:06:32’, 1485885992),
(30, NULL, 1, 4, 0, ‘Were the company that sends messages through facebook messenger on your behalf to everyone that likes, shares, comments or likes your pages paid or unpaid…’, ‘2017-01-31 18:07:10’, 1485886030),
(31, NULL, 1, 4, 0, 'Is there a good time to give you a call? Id love to explain in more detail to you about this brand new service that is guaranteed to boost your sales. ', ‘2017-01-31 18:08:27’, 1485886107),
(32, NULL, 1, 4, 0, ‘Its been a week, sorry for the delay in getting back to you but im literally signing up a hundred businesses a week all by myself so just getting caught up’, ‘2017-01-31 18:10:41’, 1485886241),
(33, NULL, 1, 4, 0, ‘LMK if you are still interested :)’, ‘2017-01-31 18:10:53’, 1485886253),
(34, ‘0C000000155296A9’, 6, 1, 1, ‘Who is this?\n🇯🇲MoneyK'aSh🇯🇲’, ‘2017-01-31 18:11:10’, 1485886270),
(35, NULL, 1, 6, 0, ‘I’'m Shane I own www.SmartSocialConnect.com You had entered your info from our Facebook ad so wanted to follow up w/ you about it. ', ‘2017-01-31 18:11:51’, 1485886311),
(36, NULL, 1, 6, 0, ‘Were the company that sends messages through facebook messenger on your behalf to everyone that likes, shares, comments or likes your pages/posts… do u recall?’, ‘2017-01-31 18:12:36’, 1485886356),
(37, NULL, 1, 1, 1, ‘test’, ‘2017-01-31 19:33:34’, 1485891214),
(38, ‘0C0000001557D773’, 1, 1, 1, ‘Got it’, ‘2017-01-31 19:34:13’, 1485891253),
(39, ‘0B00000030142263’, 9, 1, 1, ‘Hi Shane, my name is crystal. I saw your ad on Craigslist and I think its awesome and I was interested in hearing more about the details’, ‘2017-02-01 04:10:28’, 1485922228),
(88, ‘0C00000016FF7C57’, 18, 1, 0, ‘Test’, ‘2017-02-06 17:56:34’, 1486403794);
This is what I have tried
SELECT a.*
FROM ( SELECT c.id,c.contacts_name FROM sms_contacts c
LEFT JOIN sms_twoway_chat tc ON c.id = tc.receiver_id
WHERE user_id = 1 AND c.contact_type = 0
ORDER BY -tc.is_read, tc.date_time DESC
) a
UNION
SELECT b.*
FROM ( SELECT c.id,c.contacts_name FROM sms_contacts c
INNER JOIN sms_twoway_chat tc ON c.id = tc.receiver_id
WHERE user_id = 1 AND c.contact_type IN(1,2)
ORDER BY -tc.is_read,tc.date_time DESC
) b;