Postgres get related post with fallback

Gracie williams 0
Viewed 12

I wanted to get related posts from my table.So I tried some code like below

"SELECT post,category FROM library  WHERE  title LIKE '%" + query + "%' LIMIT 20"

Now sometimes it returns 20 results , but sometimes less than 20 , So when response is less than 20 , I need to fill with random posts which has category I mention , For example something like below

SELECT post,category FROM library WHERE category = 'php'  OFFSET floor(random()*20) LIMIT 20;

For example if my search query returns 5 results , it should get random 15 posts based on my 2nd query.

postgresql
1 Answers

Perhaps you can use UNION ALL?

SELECT * FROM (
  (SELECT post,category
     FROM library
    WHERE title LIKE '%" + query + "%' LIMIT 20)
  UNION ALL 
  (SELECT post,category
     FROM library
    WHERE category = 'php' OFFSET floor(random()*20) LIMIT 20)
) LIMIT 20;
richyen0
Related