Best practice in MySQL for selecting two interchangeable columns and counting them, returning the mo-CodePudding

I have a MySQL table that looks like:

CREATE TABLE `messages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `from` varchar(12) NOT NULL,
  `to` varchar(12) NOT NULL,
  `message` text,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=latin1;

So each time a message is sent or received, it is stored as:

# id    from,           to,             message,                            timestamp
'65',   ' 1231303****', ' 1833935****', 'Showtimes',                        '2022-01-26 09:26:10'
'64',   ' 1833935****', ' 1231303****', 'Showtimes are: 12:30 someresponse', '2022-01-26 09:26:10'

I want to create a index of these conversation threats, and need to be able to execute a query that selects the conversation based on it either being addressed from or to a specific number, and returns the number of rows that match either, while at the same time, returning the last message that was sent. So basically I want it to return:

recipient (the other phone number, not the one I'm using to look up ),count(messages),lastmessage

Individually, I can query this all separately, since most of my experience here resolves around using PHP to untangle the data I'm going after. What I'm curious about is a single query that lets MySQL handle this, rather than submitting multiple queries to the database server. I figure this may be a good time to approach in, since several projects I've coded have ran out of memory to process before with so many queries between so many loops.

Apologies in advance if this has been answered somewhere else already. I searched extensively for an answer, but the few results I found used a completely different table structure than I am using, and the MySQL query I was able to fumble together didn't work. I stand next to my work as a PHP programmer, but my MySQL needs some work. Hence I'm here!

CodePudding user response：

If a conversation thread can be defined by a unique combination of from and to then creating a compound key where the first node is the lower of the two then all the conversations in the thread can be established , however selecting on from OR two means many conversation threads may be selected. for example

DROP TABLE IF EXISTS T;
CREATE TABLE T(ID INT AUTO_INCREMENT PRIMARY KEY, FROMNO INT, TONO INT);

INSERT INTO T(FROMNO,TONO) VALUES
(1,2),(2,1),
(1,3),(4,1),(1,2);

WITH CTE AS
(SELECT * ,
        CASE WHEN FROMNO < TONO THEN CONCAT(FROMNO,TONO)
        ELSE CONCAT(TONO,FROMNO) 
        END AS CVAL
FROM T
WHERE FROMNO = 1 OR TONO = 1
),
CTE1 AS
(SELECT *,
          DENSE_RANK() OVER (ORDER BY CVAL) DR 
FROM CTE
),
CTE2 AS
(SELECT CVAL,COUNT(*) conversations,MAX(ID) MAXID
FROM CTE1
GROUP BY CVAL
)
SELECT CTE2.CVAL,CTE2.THINGS,CTE2.MAXID,T.ID 
FROM CTE2
JOIN T ON T.ID = CTE2.MAXID;

Yields

 ------ --------------- ------- ---- 
| CVAL | conversations | MAXID | ID |
 ------ --------------- ------- ---- 
| 13   |             1 |     3 |  3 |
| 14   |             1 |     4 |  4 |
| 12   |             3 |     5 |  5 |
 ------ --------------- ------- ---- 
3 rows in set (0.002 sec)