I am trying to call categories but only if a document is assigned to those categories.
The document has the categories stored as a JSON type. In the WHERE it is only bringing back the rows that have document uids to the user id who is looking. In this question I have set this as 1 to simply. This is also a JSON type.
It fails on the LEFT JOIN section so I cannot test if the WHERE statement is working.
CREATE TABLE IF NOT EXISTS `doccats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`pid` int(11) DEFAULT NULL,
`groupnoreason` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `doccats` (`id`, `name`, `pid`, `groupnoreason`) VALUES
(1, 'Test 1', NULL, 1),
(2, 'Test 2', NULL, 1),
(3, 'Sub Test', 1, 1),
(4, 'Inner Sub', 3, 1),
(5, 'Test 3', NULL, 1),
(6, 'Test 4', NULL, 1),
(9, 'Sub Test 2', 2, 1),
(10, 'Inner Sub 2', 2, 1),
(11, 'Sub Test 3', 5, 1),
(12, 'Inner Sub 2', 5, 1),
(13, 'Sub Test 4', 6, 1),
(14, 'Inner Sub 4', 6, 1),
(15, 'Sub Sub 1', 13, 1),
(16, 'Sub Sub 2', 13, 1),
(17, 'asdasdad', 3, 1),
(18, 'fffff', 1, 1),
(19, 'Testing INNNNNNNER', 15, 1),
(20, 'ioausdhioauhduia', 19, 1),
(21, 'dsfsdfdsfsdfsdf', 20, 1),
(22, 'fghfghfghfgh', 21, 1),
(23, 'sdfsdf', 22, 1),
(24, 'fghfghf', 23, 1),
(25, 'ghjghjhgj', 24, 1),
(26, 'hjkhjkhjk', 25, 1),
(27, '567567576', 25, 1),
(28, '678967fghfghfgh', 25, 1),
(29, '345345345453345', 24, 1);
CREATE TABLE IF NOT EXISTS `documents` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`file` varchar(255) NOT NULL,
`uids` json NOT NULL,
`duids` json DEFAULT NULL,
`suids` json DEFAULT NULL,
`cats` json NOT NULL,
`uploaded` datetime NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COMMENT='suids';
INSERT INTO `documents` (`id`, `name`, `description`, `file`, `uids`, `duids`, `suids`, `cats`, `uploaded`, `status`) VALUES
(1, 'Test Document 1', 'osidfhjosidjf soijfsiodjfsoidjfoisdjfosidjf soidjfoisdjfoisdjfoisjfij oj oij ojoijjoisdjfiosdjf sdf sdfsdfoijoi oijsdf', 'G04ClNGZIJD4n4I0TizW8kYPdGZHkVPT.pdf', '[\"1\", \"2\", \"3\"]', NULL, NULL, '[\"1\", \"2\", \"3\"]', '2023-03-11 13:46:03', 1),
(2, 'Test another', 'fdsfsdfsdfs dsfsdfsdf sdfsdfdfdf', 'lowM5s3kXstpU4XJ3mstlGpDxcVckaS5.pdf', '[\"2\", \"7\", \"9\", \"10\", \"88\"]', NULL, NULL, '[\"4\", \"10\", \"23\", \"26\"]', '2023-03-11 14:31:29', 1);
SELECT
dc.id, dc.pid, dc.name
FROM doccats dc
LEFT JOIN documents d ON JSON_CONTAINS(d.cats, CAST(dc.id AS JSON))
WHERE JSON_CONTAINS(d.uids, CAST(1 AS JSON))
ORDER BY dc.name ASC
The expected outcome of this query is to return 3 rows which would be:
dc.id, dc.pid, dc.name
(1, NULL, 'Test 1'),
(2, NULL, 'Test 2'),
(3, 1, 'Sub Test')
INNER JOIN is ok for your case
You need to search an array of strings as follows:
JSON_CONTAINS(d.uids, '"1"', '$')
SQL query :
SELECT
dc.id as catID, dc.name as catName, dc.pid, d.id as docID
FROM doccats dc
INNER JOIN documents d ON JSON_CONTAINS(cats, CONCAT("\"",dc.id, "\""))
WHERE JSON_CONTAINS(d.uids, '"1"')
ORDER BY dc.id