Search for keywords across posts

Data & reporting
1

This query will let you efficiently search for a keyword across all posts. It will be significantly faster than a posts.raw LIKE query, because it uses the postgres full text search structured data

-- [params]
-- string :query

SELECT p.id as post_id FROM posts p
LEFT JOIN post_search_data psd ON psd.post_id = p.id
WHERE psd.search_data @@ TO_TSQUERY(:query)
19 Likes
2

What is missing in the fabulous code snippet is the ability to search by rank.

-- [params]
-- string :query

SELECT p.id as post_id, 
       ts_rank(psd.search_data, to_tsquery('english', :query)) AS rank
FROM posts p
LEFT JOIN post_search_data psd ON psd.post_id = p.id
WHERE psd.search_data @@ TO_TSQUERY(:query)
ORDER BY rank DESC
1 Like