Statement with multiple joins and field with CSV in it

[mongoose00318]

Okay I thought this was working pretty well but upon modifying the data in the DB a bit to put it through it's paces I ran into some issues. I get this warning in MySQL now "Warning: #1292 Truncated incorrect DOUBLE value: '8,11'" and also the dataset it returns isn't what is expected so I am doing something wrong lol. I'm still learning to make my SQL more complex 😕

$depts_to_check = [1,2,3,5,6,7,8,10,11,12,13];
$str_depts_to_check = implode(',', $depts_to_check);

$query = "
			SELECT DISTINCT
				l.user_id,
				l.name,
				(SELECT COUNT(*) FROM chat_to_users WHERE to_user_id = l.user_id AND status = 0) AS total_unread
			FROM
				login l
			LEFT JOIN
				chat_to_users ctu
				INNER JOIN
					chat_message cm
				ON
					ctu.message_id = cm.chat_message_id
			ON
				l.user_id = ctu.to_user_id AND ctu.status = 0
			WHERE
				l.dept_code IN (". $str_depts_to_check .") 
				AND (SELECT COUNT(*) FROM chat_to_users WHERE to_user_id = l.user_id AND status = 0) > 0
				AND cm.timestamp < DATE_SUB(NOW(), INTERVAL 24 HOUR)
			ORDER BY total_unread DESC
";
$statement = $pdo->prepare($query);
$statement->execute();
$results = $statement->fetchAll();

Here's the table structure of the 3 tables.

CREATE TABLE `chat_message` (
 `chat_message_id` int(11) NOT NULL AUTO_INCREMENT,
 `order_id` int(11) DEFAULT NULL,
 `chat_message` text NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'time message was sent',
 PRIMARY KEY (`chat_message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=483 DEFAULT CHARSET=latin1

CREATE TABLE `chat_to_users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `message_id` int(11) NOT NULL,
 `order_id` int(11) DEFAULT NULL,
 `to_user_id` int(11) NOT NULL DEFAULT 0,
 `from_user_id` int(11) NOT NULL,
 `read_timestamp` timestamp NULL DEFAULT NULL COMMENT 'time user read message',
 `status` int(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=924 DEFAULT CHARSET=latin1

CREATE TABLE `login` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 `dept_code` varchar(255) NOT NULL,
 `username` varchar(255) NOT NULL,
 `name` varchar(100) NOT NULL,
 `email` varchar(100) NOT NULL,
 `password` varchar(255) NOT NULL,
 `password_force_reset` tinyint(1) NOT NULL,
 PRIMARY KEY (`user_id`),
 UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1

Also, I know where that warning originated from: 

That column (dept_code) in the login table can sometimes have a csv representation of the depts the user can belong to. Most users only have one dept_code but there are a few exceptions that have it like that. I'm not sure if I can work the query to work under this scenario?

[requinix]

Don't put multiple pieces of data (multiple departments) into one field (dept_code). The correct way to deal with that sort of situation is to create a new table where each row contains (1) the user and (2) one of their departments. Most people will have only one row in the table but some will have more.

[mongoose00318]

@requinix 😕 I thought you may say that...I was thinking the same thing. Is there a temporary way to accomplish this until I can change the db structure as you said? There are other parts of the application I'll have to change to match that.

[requinix]

I suspect there's a cleaner way, but the only thing I can think of now is to turn those department IDs into a fake JOINed table (using a SELECT 1 UNION SELECT 2 UNION... chain, or VALUES), then use FIND_IN_SET.

[mongoose00318]

@requinix What do you mean by a fake joined table?

[requinix]

SELECT...
FROM...
JOIN (
  SELECT 1
  UNION SELECT 2
  UNION SELECT 3
) AS not_an_actual_table

There's a term for that subquery but I don't remember what it is.

[Barand]

Table subquery ?

[mongoose00318]

Whew..okay trying to make sense of it. Just looked at the MySQL ref. manual on VALUES..as you suggested. That is interesting...but FIND_IN_SET only takes 1 needle to look for in the haystack, right?

So how would I iterate over a list of values within the SQL statement itself? Hopefully I asked all that right.

[requinix]

That's why you would need that table subquery: to break your set of desired departments into single values. Then you can FIND_IN_SET each of those values inside the dept_code list.

[Barand]

DATA

TABLE: mongoose
+----+---------+
| id | testcol |
+----+---------+
|  1 | 1       |
|  2 | 2       |
|  3 | 3       |
|  4 | 2,3     |
|  5 | 1,4     |
|  6 | 2,5     |
|  7 | 1,3     |
|  8 | 2,4     |
+----+---------+

 

Find records in mongoose where testcol contains 1 or 3 ...

mysql> SELECT DISTINCT id, testcol
    -> FROM mongoose
    ->      JOIN (
    ->             SELECT 1 as n
    ->             UNION SELECT 3
    ->           ) nums ON FIND_IN_SET(n, testcol)
    -> ;
+----+---------+
| id | testcol |
+----+---------+
|  1 | 1       |
|  3 | 3       |
|  4 | 2,3     |
|  5 | 1,4     |
|  7 | 1,3     |
+----+---------+

edit:

The subquery effectively gives you a temporary table "nums" ...

TABLE: nums
+---+
| n |
+---+
| 1 |
| 3 |
+---+

 

Edited July 11, 2020 by Barand

[mongoose00318]

@Barand @requinix Awesome. I'll give it a try. Thank you!