# 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