5

I've tried using many symbols to separate columns; ||, |, &&, & with and without spaces.

For instance

.textSearch("username, title, description", "...");
.textSearch("username|title|description", "...");

And nothing has worked :(

Lunandd
  • 55
  • 1
  • 6

1 Answers1

6

You could create a SQL function to perform search like this:

create or replace function search_posts(keyword text)
returns setof posts
as
$func$
select 
  * 
from 
  posts
where 
  to_tsvector(username || ' ' || title || ' ' || description) -- concat columns, but be sure to include a space to separate them!
  @@ to_tsquery(keyword);
$func$
language sql;

You can call this function like this:

const {data, error} = await supabase.rpc('search_posts', { keyword: '[YOUR_SEARCH_TERM_HERE]' })

You can read more about textSearch here

dshukertjr
  • 15,244
  • 11
  • 57
  • 94
  • 1
    Thanks for this. Also if you want to return all the columns of the `posts` table from the `select` statement, you can use `returns setof posts` rather than `returns table([YOUR_TABLE_DEFINITION])` – c0deblooded Dec 01 '22 at 13:59
  • does this still work? and can I still add some sql pattern like `await supabase.rpc('search_posts', { keyword: '[YOUR_SEARCH_TERM_HERE]' }).where`? – Jenuel Ganawed Apr 30 '23 at 08:47
  • 1
    @JenuelGanawed It should work just fine, and yes you can add additional where clause using filters in Supabase. https://supabase.com/docs/reference/javascript/using-filters – dshukertjr Apr 30 '23 at 12:55