David Bernheisel
Postgres 12 Full Text Search with Ecto
Postgres can perform quick full-text search across columns with the help of generated columns.
# Setup the migration
execute """
ALTER TABLE tips
ADD COLUMN searchable tsvector
GENERATED ALWAYS AS (
to_tsvector('english', coalesce(title, '') || ' ' || coalesce(description, ''))
) STORED
""",
"ALTER TABLE tips DROP COLUMN searchable"
create index("tips", ["searchable"],
name: :tips_searchable_index,
using: "GIN",
concurrently: true
)
# Use it in your queries
import Ecto.Query
def search(queryable \\ Tip, search_terms) do
queryable
|> where(
[q],
fragment("? @@ websearch_to_tsquery('english', ?)", q.searchable, ^search_terms)
)
|> order_by([q], [
asc: fragment(
"ts_rank_cd(?, websearch_to_tsquery('english', ?), 4)", q.searchable, ^search_terms),
desc: q.published_at
])
end
57
upvotes