Recommended posts example in PostgreSQL using full text search, based on a single post

recommended_posts.sql

-- 
-- Replace 1 with the post id
-- 
WITH
  posts_query AS (
      SELECT
          to_tsvector(posts.title) AS posts_title,
          to_tsvector(posts.tags) AS posts_tags
      FROM posts
  ),
  post AS (
      SELECT
          id,
          title,
          user
      FROM posts
      WHERE
          id = 1
  )
SELECT
  JSON_BUILD_OBJECT (
      'id', posts.id,
      'user', posts.user,
      'title', posts.title,
      'description', posts.description,
      'picture', posts.picture,
      'created_at', posts.created_at
  ),
  ts_rank_cd(to_tsvector('english', posts.title), plainto_tsquery(posts.title)) AS title_score,
  ts_rank_cd(to_tsvector('english', posts.user), plainto_tsquery(posts.user)) AS user_score,
  ts_rank_cd(to_tsvector('english', posts.tags), plainto_tsquery(posts.tags)) AS tags_score
FROM
  posts,
  posts_query,
  post
WHERE
  (
      posts.id <> 1
  )
  AND
  (
      plainto_tsquery(post.title) @@ posts_title
  )
ORDER BY
  title_score DESC,
  tags_score DESC,
  user_score
LIMIT 12