Jump to content
mongoose00318

Statement with multiple joins and field with CSV in it

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?

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

@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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites

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.