Last active
August 29, 2015 14:20
-
-
Save timonv/1f5e7ebf230bcf079939 to your computer and use it in GitHub Desktop.
Postgresql search hstore values
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 0. Create a bunch of id => vector results for every definition | |
WITH unnested_keys_as_vector AS ( | |
SELECT id as term_id, to_tsvector((each(definitions)).value) as term | |
FROM glossary_terms | |
WHERE glossary_id = 10 | |
) | |
-- 2. Select the rank so we can sort by it (default order is order of terms in table) | |
SELECT *, ts_rank_cd(term, to_tsquery('I | like | bananas')) as rank | |
FROM glossary_terms | |
-- 1. Join the result of the tsvector search on the terms | |
JOIN (SELECT term_id, term FROM unnested_keys_as_vector | |
WHERE term @@ to_tsquery('I | like | bananas') ) uks ON glossary_terms.id = term_id | |
WHERE glossary_id = #{id} | |
-- 3. Order by rank so we get best matching first | |
ORDER BY rank DESC |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment