I would like to specify which row to return first based on whether the uid value is equal to a value I specify, when using count with group by.
I have 5 tables. Two tables are 'intersection entity' tables that hold foreign keys to the 3rd and 4th tables poster and albums, as well as the foreign key to the fifth table, link which holds the user_id.
if a user has created a link, I need that user_id to be the value in the user_id column, otherwise it doesn't matter which row is used. I have tried many different approaches and the only one that comes close is using group by imageId, uid = 9, but this breaks the count and I get duplicate rows with a count of 1, instead of the actual count. I apologize if I am missing something obvious as I haven't used mysql significantly in quite awhile.
SELECT * FROM (
SELECT poster.*, l.user_id as uid, count(*) as c
FROM `poster_links` pl
INNER JOIN poster ON poster.posterID = pl.poster_id
inner join link l on pl.link_id = l.link_id
WHERE pl.poster_id != 167 and l.link_id in(
SELECT pl.link_id from poster_links as pl where poster_id = 167)
group by imageId, uid = 9
UNION ALL
SELECT albums.*, l.user_id as uid, count(*) as c
FROM `album_links` al
INNER JOIN albums ON albums.album_id = al.album_id
inner join link l on al.link_id = l.link_id
WHERE al.album_id != 'null' and l.link_id in(
SELECT pl.link_id from poster_links pl where poster_id = 167)
group by imageId, uid = 9
) T
Using group by imageId, uid = 9(current user) is the only way I have found to force the uid to be equal to the current user if that user is one of the multiple users who have created the link. I am trying to avoid an additional query to get the current users 'links', then comparing both query results on the back end.
I am grouping by imageId because it is the only unique field. Also I have tried using case in the select-
CASE l.user_id
WHEN 9 THEN 9
ELSE null
END as uid,
but I only got null values probably because mysql only checks the first value returned which could be any uid.
CREATE TABLE `poster` (
`posterID` int(11) NOT NULL,
`imageId` varchar(255) DEFAULT NULL,
`title` varchar(64) NOT NULL,
`user` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `poster` (`posterID`, `imageId`, `title`, `user`) VALUES
(71, 'C44FB5C807FFC1768271D9988FF41270054D9B6F', 'Testarossa', 1),
(72, 'E009F63FD85E1EE98BE1EED77EA4F8A8E96BEA4F', 'Lambo', 1 ),
(73, 'E192493089403C89E9DAA8B64DDBED12FFF818E5', 'Ferrari', 1),
(74, '8AFFA17561689FCA48BB9000B81AE13CA46E9A05', 'Koenisegg', 1),
(167, 'E13CA46E98AFFA17561689FCA48BB9000B81AA05', 'egg', 1);
CREATE TABLE `albums` (
`album_id` int(11) NOT NULL,
`imageId` varchar(255) DEFAULT NULL,
`title` varchar(255) NOT NULL,
`user_id` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `albums` (`album_id`, `imageId`, `title`, `user_id`) VALUES
(45, '6FJIVC7L4OKNM8YAP1XGZ23DW5SUR0QEHB9', 'Cars', 3),
(46, 'FU6D7MVTZK85SRPLIE1CXGY2WO4QAN03JHB', 'Misc', 3),
(47, '5W498FRDAQGOZN2XUIP7VL3CKJTHSBM0E16', 'SCars', 3),
(48, 'SG8MVX1DLAUHENFOI5JB6W0C2RP379QYZ4K', 'MCars', 3);
CREATE TABLE `link` (
`link_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `link` (`link_id`, `user_id`, `timestamp`) VALUES
(11, 6, '2019-10-29 09:11:25'),
(12, 3, '2019-10-29 07:57:24'),
(13, 9, '2019-10-29 07:59:55'),
(14, 9, '2019-10-29 09:10:25'),
(15, 7, '2019-11-03 11:05:51'),
(16, 9, '2019-11-03 12:05:51');
CREATE TABLE `album_links` (
`link_id` int(11) NOT NULL,
`album_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `album_links` (`link_id`, `album_id`) VALUES
(12, 45),
(13, 45),
(13, 46),
(14, 47),
(15, 48);
CREATE TABLE `poster_links` (
`link_id` int(11) NOT NULL,
`poster_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `poster_links` (`link_id`, `poster_id`) VALUES
(11, 167),
(11, 71),
(12, 167),
(14, 167),
(12, 71),
(16, 167),
(16, 71),
(14, 72),
I expect to get all the posters and albums grouped by imageId with the addition of a count column and a uid column that should display the value 9, if that is one of the values in the link table. Instead it shows a different uid.
poster | uid | c |
C44FB5.. 9 3
E009F6.. 9 1
6FJIVC.. 3 1
5W498F.. 9 1
Best Answer
I'm still having trouble to understand the logic behind your expected result, but here is a wild shot:
I placed you original query in a Common Table Expression (CTE) and used a Window function for the count.
I split it into 2 cases, where the uid = 9 and images where uid = 9 does not have a link:
Fiddle