0

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
  • after trying multiple different websites, I finally came back to yours and got a schema built and even got results! Of course they are the wrong results, but here they are- dbfiddle.uk/… – jmoney Mar 5 at 17:11
  • Here is another fiddle. The count function works perfectly fine in phpmyadmin, yet here it repeats the same rows instead of counting them. dbfiddle.uk/… The select statement is EXACTLY the same in phpmyadmin. Yet the count is accurate and there are no repeating rows. How can that be? – jmoney Mar 5 at 17:33
  • @jmoney Your table definitions not only have invalid syntax but do not include any PRIMARY KEY, FOREIGN KEY, or other information, despite different parts of your answer suggesting such things exist. Please include the complete table definitions, as which columns are unique may be relevant to your question. – Willem Renzema Mar 5 at 19:36
  • The problem is I am trying to reproduce the schema as concise as possible. I mean it's just not practical to reproduce everything exactly as there is a ton of data and additional tables. There is a user table as well. I can not fathom how any of this is necessary for the select statement I am attempting. After adding the keys the select statement is now broken. Can you please infer what I'm trying to do with what I have. I'm pretty burnt out on this and may try one last time tomorrow or I might have to move on. dbfiddle.uk/… – jmoney Mar 5 at 20:29
  • Actually didn't break select statement instead error: Table 'fiddle_FYLPGKQEOKUVOKEQLAGM.poster_links' doesn't exist – jmoney Mar 5 at 20:36
0

I'm still having trouble to understand the logic behind your expected result, but here is a wild shot:

WITH tmp as (
    SELECT poster.imageId,  l.user_id as uid
    FROM poster_links t
    JOIN poster 
        ON poster.posterID = t.poster_id
    JOIN link l 
        ON t.link_id = l.link_id
    WHERE t.poster_id <> 167 
      AND t.link_id IN (
          SELECT pl.link_id 
          FROM poster_links as pl 
          JOIN link AS l 
              ON l.link_id = pl.link_id 
          WHERE poster_id = 167
      )
    UNION ALL
    SELECT albums.imageId, l.user_id as uid
    FROM `album_links` s
    JOIN albums 
        ON albums.album_id = s.album_id
    JOIN link l 
        ON s.link_id = l.link_id 
    WHERE s.album_id <> 'null' 
      AND s.link_id in (
           SELECT pl.link_id 
           FROM poster_links AS pl 
           JOIN link AS l 
               ON l.link_id = pl.link_id 
           WHERE poster_id = 167
       )
)
SELECT imageId, uid, cnt
FROM (
    SELECT imageId, uid
         , COUNT(*) OVER (PARTITION BY imageId) as cnt
    FROM tmp t1
) as t
WHERE uid = 9
UNION ALL
SELECT imageId, uid
     , COUNT(*) OVER (PARTITION BY imageId) as cnt 
FROM tmp t1
WHERE NOT EXISTS (
    SELECT 1 FROM tmp t2
    WHERE uid = 9
      AND t1.imageId = t2.imageId
);

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:

imageId                                  uid    cnt
5W498FRDAQGOZN2XUIP7VL3CKJTHSBM0E16        9    1
C44FB5C807FFC1768271D9988FF41270054D9B6F   9    3
E009F63FD85E1EE98BE1EED77EA4F8A8E96BEA4F   9    1
6FJIVC7L4OKNM8YAP1XGZ23DW5SUR0QEHB9        3    1

Fiddle

| improve this answer | |
  • Thank you very much for your solution. I took a break this weekend and when I checked stack exchange I didn't see an alert so I assumed no one had answered or commented. I decided to just refactor my query into several different ones and perform the logic on my backend. While doing this I decided to give it one last shot and came up with an answer! I simply needed to put an additional aggregate function in the field list with a case structure and test for uid. If you see anything wrong or that could be improved on, please let me know. – jmoney Mar 8 at 15:38
0

I took a 2 day break and when I checked back this morning I didn't see any additional alerts so I decided to give up and break the query into several different queries and perform the needed logic in my back end code. While working on this I made a few more attempts and found a solution! It seems all I needed to do was put an additional aggregate function in the field list and test for the needed uid.

SUM(CASE WHEN l.user_id = 9 THEN 9 ELSE 0 END) as uid

Here is what I did-

SELECT  * FROM (
                    SELECT poster.*, 1 AS type, SUM(CASE WHEN l.user_id = 9 THEN 9 ELSE 0 END) as uid, l.link_id, count(*) as c
                    FROM `poster_links` t
                    INNER JOIN poster ON poster.posterID = t.poster_id
                    inner join link l on t.link_id = l.link_id
                    WHERE t.poster_id != 167 and t.link_id in(
    SELECT pl.link_id from poster_links as pl join link as l on l.link_id = pl.link_id where poster_id = 167)
        group by imageId
    UNION ALL
                    SELECT albums.*, 2 AS type, SUM(CASE WHEN l.user_id = 9 THEN 9 ELSE 0 END) as uid, l.link_id, count(*) as c
                    FROM `album_links` s
                    INNER JOIN albums ON albums.album_id = s.album_id
                    inner join link l on s.link_id = l.link_id 
                    WHERE s.album_id != 'null' and s.link_id in(
    SELECT pl.link_id from poster_links as pl join link as l on l.link_id = pl.link_id where poster_id = 167)
        group by imageId
                    ) T

Unfortunately I have no idea how efficient this is, or will be when the tables become enormous, but I am happy I found a simple solution to the problem.

I am also curious if there is a way to do this without running the exact same subquery twice as both select poster.* and select album.* queries are dependent on the same subquery. Any suggestions?

If anyone has any comments on my table structure or the efficiency of this query, or anything else, please provide feedback!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.