Add a method to search across multiple columns

[dshukertjr]

Feature request

Is your feature request related to a problem? Please describe.

Currently, textSearch allows to search for a search query on only one column, but in a typical application, we would want to search across multiple columns.

Currently, searching through multiple columns is possible using the rest api like this PostgREST/postgrest#2374 (comment)

GET /blog_posts?or=(title.fts.searchTerm,content.fts.searchTerm)

That means we can perform full text search across multiple columns via js client can be done like this:

const { data, error } = await supabase
  .from('blog_posts')
  .select('*')
  .or('title.fts.searchTerm,content.fts.searchTerm')

However, developers would need to use the rest api syntax to perform text search, so it might not be a nice developer experience.

Describe the solution you'd like

This is a suggestion from @steve-chavez, so I don't want to take any credits, but we could introduce a new method where we combine multiple text search just like match like this:

const { data, error } = await supabase
  .from('blog_posts')
  .select('*')
  .textMatch({title: 'sometitle', contents: 'somecontent'})

Describe alternatives you've considered

We can use or filter to combine multiple text search query.

const { data, error } = await supabase
  .from('blog_posts')
  .select('*')
  .or('title.fts.searchTerm,content.fts.searchTerm')

[soedirgo]

Sounds reasonable to me. Simplest solution would be to extend the current textSearch() to accept an array of columns for the column argument:

const { data, error } = await supabase
  .from('blog_posts')
  .select()
  .textSearch(['title', 'contents'], 'searchTerm')

[steve-chavez]

@soedirgo So title and contents would be combined with OR right? Hm, seems a bit ad hoc to me. At first glance I would also think that both should be combined with AND.

Perhaps we can come up with an uniform interface for all filters. Instead of taking an array as an alternative, how about an object? Like:

.textSearch({
  or: {
    title: 'searchTerm'
  , contents: 'searchTerm'
  }
});

[soedirgo]

Hmm, it departs too much from how the current filters look like imo (personal taste of course). Though I can see the confusion of or/and with the array approach.

[dshukertjr]

I actually like @soedirgo's suggestion here, because I think in most cases, we need to search for only one search query across different columns. About the confusion of or and and, would in code documentation be not enough?

const { data, error } = await supabase
  .from('blog_posts')
  .select()
  .textSearch(['title', 'contents'], 'searchTerm')

[steve-chavez]

I think in most cases, we need to search for only one search query across different columns

Yeah, since it's a common use case for textSearch then I think it should be fine adding a shortcut for it.

[steve-chavez]

This tweet asks for something similar for the like operator. I guess like:

const { data, error } = await supabase
  .from('blog_posts')
  .select()
  .ilike(['title', 'contents'], 'Jo%')

[riodw]

Any updates on this? I need it Lol

[riodw]

Might I suggest:

const { data, error } = await supabase
  .from('books')
  .select()
  .textSearch(`'title' | 'description'`, `'little' | 'big'`);

This is a simple modification of https://supabase.com/docs/guides/database/full-text-search#match-any-search-words

To find all books where field description OR title contain ANY of the words little OR big.

[kvetoslavnovak]

I agree that the lack of this feature is really really annoying. To use an SQL function for this is rather overkill.
There are already SackOverflow questions and even otherwise nice examples in official docs do not explicitly mention and warn that there is no JavaScript and Dart front-end api for more columns search.
At this moment it would be great to have a explicit warning in docs that this is really a non existing feature, I made a PR #12508

[shrey150]

Building a project where this feature would be greatly appreciated. Might use the workaround approach with a custom Postgres function, but this really should be a first-class function in the API

[steve-chavez]

Just to document how you can do this now:

CREATE FUNCTION title_description(books) RETURNS text AS $$
  SELECT $1.title || ' ' || $1.description;
$$ LANGUAGE SQL;

const { data, error } = await supabase
  .from('books')
  .select()
  .textSearch('title_description', `little`)

https://supabase.com/docs/guides/database/full-text-search#search-multiple-columns

[lucksp]

how do you all accomplish this now, without the new feature? Use multiple queries and then add logic to filter/combine?