Elixir Tips

Elixir Tips

  • 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