I am worried that I think about SQL wrongly. I am able to hack queries together to do what I want, but they end up quite ludicrous with multiple nested selects. Here is an example that I can’t fathom how to simplify (let alone how to improve the performance). One of my main issues was how to pick the most recent message out of the union when I group the users together. Wrapping it in another select just to order it works, but seems too ugly and I am worried about performance with doing that all the time. My plain english explanation of what the query does is:

Looks at sent and received messages relevant for a given user (in this case hard-coded to user id 1), and returns a distinct list of all users that have messaged me along with when the most recent message was either sent or received between us.

The uid field that I am storing in another meta table unfortuantely has to be returned too, making the whole thing even more complex:

SELECT ID, user_nicename, MAX(last_message) AS last_message, uid FROM
    (SELECT * FROM 
        (SELECT wp_users.ID, wp_users.user_nicename, MAX(time) as last_message, (SELECT meta_value FROM wp_usermeta WHERE user_id = recipient_user_id AND meta_key = 'uid') AS uid FROM `wp_usermessages`
            LEFT JOIN wp_users ON wp_users.ID = recipient_user_id
            WHERE sender_user_id = 1
            GROUP BY recipient_user_id, wp_users.user_nicename
        UNION
        SELECT wp_users.ID, wp_users.user_nicename, MAX(time) as last_message, (SELECT meta_value FROM wp_usermeta WHERE user_id = sender_user_id AND meta_key = 'uid') AS uid FROM `wp_usermessages`
            LEFT JOIN wp_users ON wp_users.ID = sender_user_id
            WHERE recipient_user_id = 1
            GROUP BY sender_user_id, wp_users.user_nicename
        ) SentReceived
     ORDER BY last_message) SentReceivedOrdered
GROUP BY ID, user_nicename, uid
ORDER BY `last_message`

Fiddle

db fiddle here: https://www.db-fiddle.com/f/6z7jVN6DtUYcuZwSXxeMX7/0

Sample data:

CREATE TABLE `wp_usermessages` (
  `id` bigint(20) NOT NULL,
  `time` datetime NOT NULL,
  `sender_user_id` bigint(20) UNSIGNED NOT NULL,
  `recipient_user_id` bigint(20) UNSIGNED NOT NULL,
  `message` text NOT NULL,
  `message_read` bit(1) NOT NULL DEFAULT b'0');

INSERT INTO `wp_usermessages` (`id`, `time`, `sender_user_id`, `recipient_user_id`, `message`, `message_read`) VALUES
(1, '2019-02-26 08:29:57', 2, 1, 'Hi, just wanted to check how you are?', b'0'),
(2, '2019-02-28 08:29:24', 1, 2, 'good thank you', b'0'),
(2, '2019-03-28 08:29:24', 1, 2, 'already been a month', b'0'),
(2, '2019-02-28 08:29:24', 1, 2, 'good thank you', b'0'),
(3, '2019-02-28 08:30:05', 5, 1, 'hi', b'1'),
(3, '2019-02-28 08:00:00', 5, 1, 'anyone', b'1'),
(3, '2019-02-28 08:05:00', 5, 1, 'hello', b'1');


CREATE TABLE `wp_usermeta` (
  `umeta_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext
);

INSERT INTO `wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES
(73, 1, 'uid', '9f39aa0ecd89d45e'),
(74, 5, 'uid', '0000000000000000'),
(75, 2, 'uid', '1212121212121212');

CREATE TABLE `wp_users` (
  `ID` bigint(20) UNSIGNED NOT NULL,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(255) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL,
  `user_activation_key` varchar(255) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT ''
);

INSERT INTO `wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES
(1, 'ryan', '-', 'ryan', '-', '', '2018-12-05 08:13:11', '', 0, 'ryan'),
(2, 'jim', '-', 'jim', '-', '', '2019-02-01 12:43:29', '', 0, 'test'),
(5, 'bob', '-', 'bob', '-', '', '2019-02-02 09:17:54', '', 0, 'test2');

Answer

Another possible and simpler way would be

SELECT
    tbl.conv_with,
    MAX(tbl.last_message),
    u.user_nicename,
    meta.meta_value
FROM (
        -- select the latest message for each chat
        SELECT ( CASE
                    WHEN um.recipient_user_id = 1
                        THEN um.sender_user_id
                        ELSE um.recipient_user_id
                 END
               ) AS conv_with,
               um.time AS last_message
        FROM wp_usermessages um
        WHERE um.sender_user_id = 1 OR um.recipient_user_id = 1
    ) AS tbl
-- join wp_usermeta to get meta_data for message
LEFT JOIN wp_usermeta meta ON meta.user_id = tbl.conv_with
        AND meta.meta_key = 'uid'
-- join wp_users to get user_nicename for message
LEFT JOIN wp_users u ON u.id = tbl.conv_with
GROUP BY tbl.conv_with,u.user_nicename,meta.meta_value;

Is there a better way to simplify many nested select statements?

Fiddle

Advertisement

Answer