Last active
August 29, 2015 14:07
-
-
Save mwean/8dd8a8a825fa2ce9b3b6 to your computer and use it in GitHub Desktop.
Code Snippets for Adventures in Searching with Postgres - Part 1
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
Bitmap Heap Scan on icd_codes (cost=7.63..900.68 rows=10 width=143) | |
Filter: ((code)::text ~~ 'A1%'::text) | |
-> Bitmap Index Scan on index_code_on_icd_codes (cost=0.00..7.62 rows=333 width=0) | |
Index Cond: (((code)::text ~>=~ 'A1'::text) AND ((code)::text ~<~ 'A2'::text)) |
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
Arel::Nodes::InfixOperation.new('LIKE', ICDCode.arel_table[:code], "#{query.upcase}%")) |
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
Seq Scan on icd_codes (cost=0.00..3425.00 rows=10 width=143) | |
Filter: ((code)::text ~~ 'A123%'::text) |
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
ICDCode.where(code_matches_any('A123', 'B53', 'C9')) |
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
SELECT * FROM icd_codes WHERE (((code LIKE 'A123%' | |
OR code LIKE 'B53%') | |
OR code LIKE 'C9%')) |
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
def code_matches_any(*codes) | |
first_code, *other_codes = *codes | |
other_codes.reduce(code_matches(first_code)) { |a, e| a.or(code_matches(e)) } | |
end | |
def code_matches(word) | |
Arel::Nodes::InfixOperation.new('LIKE', ICDCode.arel_table[:code], "#{word.upcase}%") | |
end |
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
ICDCode.where(ICDCode.arel_table[:code].matches("#{query}%")) |
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
SELECT * FROM icd_codes WHERE code ILIKE 'A123%' |
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
Index Scan using index_code_on_icd_codes on icd_codes (cost=0.29..8.31 rows=10 width=334) | |
Index Cond: (((code)::text ~>=~ 'A123'::text) AND ((code)::text ~<~ 'A124'::text)) | |
Filter: ((code)::text ~~ 'A123%'::text) |
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
CREATE INDEX index_code_on_icd_codes ON icd_codes (code varchar_pattern_ops) |
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
ICDCode.where(code_matches_any('A123', 'B53', 'C9')).select([:id, :code, :description]) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment