Jump to content

Statement with multiple joins and field with CSV in it


mongoose00318

Recommended Posts

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: image.png.2dc0048e25c9cc66de75e48b190e6222.png

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.