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