Mixing results from 2 different MySQL queries for a user feed-CodePudding

I want to make a primitive personalized feed in a NodeJS MySQL website. Now I just select all the posts that have specific tags in them:

SELECT column1, column2... 
FROM table 
WHERE tags = users_tags 
ORDER BY relavance 
LIMIT 8;

I want to also throw in a couple of popular posts eg:

SELECT column1, column2... 
FROM table 
ORDER BY relevance 
LIMIT 2;

I don't want to use UNION because I want to retain the ordering from my first select and insert a popular result for every 5th post. Eg.: relevant, relevant, relevant, relevant, popular, relevant...

For now, I've results1.concat(results2) which adds them as the last two, and returned it. Then I had a for loop that would append the to HTML normally for the first 4 and then one from the back for every 5th.

Is there any better solution?

CodePudding user response：

What you could use, is row_number to define the sortorder

SELECT column1,column2
FROM
    (SELECT column1,column2,IF(rn = 1, B,D) as sortorder
    FROM
    (SELECT column1, column2, ROW_NUMBER() OVER(ORDER BY relavance) rn
    FROM table1 
    ORDER BY relavance 
    LIMIT 2) t1
    UNION
    SELECT column1,column2,IF(rn < 5,  A,C) as sortorder
    FROM
    (SELECT column1, column2, ROW_NUMBER() OVER(ORDER BY relavance) rn 
    FROM table1 
    WHERE tags = users_tags 
    ORDER BY relavance 
    LIMIT 8) t2) t3
ORDER BY sortorder

CodePudding user response：

You can use ROW_NUMBER() window function to rank separately the posts with specific tags and the popular posts and then do a conditional sort:

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY tags = users_tags ORDER BY relevance) rn
  FROM tablename  
)
SELECT *
FROM cte
ORDER BY CASE 
  WHEN tags = users_tags THEN rn   FLOOR((rn - 1) / 4)
  ELSE (4   1) * rn  
END
LIMIT 10;

Change 4 to the number of posts with specific tags that you want in each block.

See a simplified demo.