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 https://github.com/PostgREST/postgrest/issues/2374#issuecomment-1186255788

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?

[Strijdhagen]

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

@steve-chavez is it possible that this doesn't work for a materialized view? I'm getting the following error:

code : "42703" details : null hint : null message : "column search_1.name_slug does not exist"

EDIT: I fixed this issue by creating an index per the documentation. This works just fine for materialized view!

[lucksp]

any update on this feature being added to roadmap &/or implemented?

EDIT: I worked with ChatGPT to come up with this function, which is a "fuzzy" search across multiple columns:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE OR REPLACE FUNCTION fuzzy_search_patterns_by_name(term TEXT)
RETURNS TABLE (
    name TEXT,
    label TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.name,
        p.label
    FROM
        pattern p
    WHERE
        similarity(p.name, term) > 0.3
    OR
        EXISTS (
            SELECT 1
            FROM unnest(string_to_array(p.alt_term, ', ')) AS alt
            WHERE similarity(alt, term) > 0.3
        );
END;
$$ LANGUAGE plpgsql;

In this version: The string_to_array() function is used to split the alt_term column into an array of individual terms based on commas and spaces (, ). The unnest() function is used to transform the array into a set of rows. The similarity() function is applied to each individual term from the alt_term column, and if any of them have a similarity greater than 0.1 with the search term, the corresponding row is included in the result. Additionally, the similarity between the name column and the search term is checked as before. This approach allows for more granular matching by considering each term within the comma-separated alt_term column individually. Adjust the similarity threshold as needed for your specific requirements.

then in my app:

await supabase
        .rpc('fuzzy_search_patterns_by_name', {
          term: debouncedSearchTerm,
        })

[bensontrent]

@lucksp Thanks a lot for that tip. Your solution works beautifully for returning partial text and multiple word searches across multiple columns. I modified your function to allow for selecting by account and test mode and pagination. Here's what I came up with:

My SQL Function

CREATE OR REPLACE FUNCTION fuzzy_search_shipments(
    term TEXT,
    account_id TEXT,
    is_test_mode BOOLEAN,
    start_index INTEGER,
    end_index INTEGER
)
RETURNS SETOF shipments AS $$
BEGIN
    RETURN QUERY
    SELECT
        s.*
    FROM
        shipments s
    WHERE
        s.account_id = fuzzy_search_shipments.account_id
    AND
        s.is_test_mode = fuzzy_search_shipments.is_test_mode
    AND
        (
            similarity(s.to_email, term) > 0.2
        OR
            similarity(s.to_company, term) > 0.2
        OR
            similarity(s.to_name, term) > 0.2
        )
    ORDER BY
        s.created_at DESC
    LIMIT (end_index - start_index + 1)
    OFFSET start_index;
END;
$$ LANGUAGE plpgsql;

In my Typescript I request one more row than specified by the pageSize and page so I can provide a has_more boolean in my response. If the returned results returns more than the length of rows specified, I remove the last row and return the response with the has_more Boolean set to true. Is for an express.js app

  async fuzzySearch(req: Request, res: Response) {
    const page = req.query.page ? parseInt(req.query.page + '') : 1; // Get page number from query parameter
    const pageSize = req.query.pageSize ? parseInt(req.query.pageSize + '') : 10; // Set the number of items per page
    const searchTerm = req.query.searchTerm ? (req.query.searchTerm + '').toLowerCase() : ''; // Get search term from query parameter
    const mode = req.query.mode === 'test' ? true : false;

    try {
        if (supabase && accountId) {
            const { data: shipments, error } = await supabase
            .rpc('fuzzy_search_shipments', {
              term: searchTerm,
              account_id: accountId as string,
              is_test_mode: mode,
              start_index: (page - 1) * pageSize,
              end_index: page * pageSize,
            })

            if (error) {
                throw error.message;
            }

            let has_more = false;

            if (shipments && shipments.length > pageSize) {
                has_more = true;
                shipments.pop();

            }

            res.send({
                has_more: has_more,
                data: shipments,
                searchTerm: req.query.searchTerm
            });
        }

    } catch (error) {
        console.error(error);
        res.status(500).send({
            error: error,
            data: null
        });
    }