This repository has been archived on 2023-11-26. You can view files and clone it, but cannot push or open issues or pull requests.
bdfr-browser/priv/repo/migrations/20231009190058_add_post_comment_fulltext_search.exs
Daniel Kempkens d40db4ca4c
All checks were successful
Build / build (push) Successful in 3m46s
feat(search): use postgres native fulltext search
2023-10-09 21:51:53 +02:00

53 lines
1.5 KiB
Elixir

defmodule BdfrBrowser.Repo.Migrations.AddPostCommentFulltextSearch do
use Ecto.Migration
def up do
# Posts
execute """
ALTER TABLE posts
ADD COLUMN searchable_content tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('german', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(selftext, '')), 'B') ||
setweight(to_tsvector('german', coalesce(selftext, '')), 'B') ||
setweight(to_tsvector('simple', coalesce(author, '')), 'C')
) STORED;
"""
execute """
CREATE INDEX posts_searchable_content_index ON posts USING gin(searchable_content);
"""
# Comments
execute """
ALTER TABLE comments
ADD COLUMN searchable_content tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(body, '')), 'A') ||
setweight(to_tsvector('german', coalesce(body, '')), 'A') ||
setweight(to_tsvector('simple', coalesce(author, '')), 'B')
) STORED;
"""
execute """
CREATE INDEX comments_searchable_content_index ON comments USING gin(searchable_content);
"""
end
def down do
# Posts
drop index("posts", [:searchable_content])
alter table(:posts) do
remove :searchable_content
end
# Comments
drop index("comments", [:searchable_content])
alter table(:comments) do
remove :searchable_content
end
end
end