How to do a full text search across all fileds in a collection?

[Mys7erio]

How to do a full text search across all fileds in a collection?

I want to enable full text search so that I can query the entire database for a specific search_term. If the search_term is present in any field of a record, I want pb to return the record.

Live Example

An excellent example of this is how pocketbase itself allows us to use a searc_term or a search_filter.

[ganigeorgiev]

PocketBase doesn't have support for fts at the moment (see #193).

The search in the Admin UI works by generating behind the scenes ~ filter rules for each field with the provided search term. For example:

id ~ "searchTerm" || name ~ "searchTerm" || rollno ~ "searchTerm" || phoneno ~ "searchTerm" || priority ~ "searchTerm" || non_priority ~ "searchTerm" || discord ~ "searchTerm"

[ganigeorgiev]

If you use JS SDK v0.19.0+ or Dart SDK v0.16.0+, the above can be slightly simplified using the new binding parameters pb.filter() helper. Here is an example with the JS SDK:

const searchTerm = "...";

await pb.collection("members").getList(1, 20, {
    filter: pb.filter(
        "id ~ {:search} || name ~ {:search} || rollno ~ {:search} || phoneno ~ {:search} || priority ~ {:search} || non_priority ~ {:search} || discord ~ {:search}",
        { search: searchTerm }
    )
})

But note that you still have to manually list all fields you want to wildcard search.

[Mys7erio]

Thanks a lot. I did end up implementing FTS this way.

[kilroyjones]

I have a query like this:

const movies = await locals.pocketbase.collection('movies').getList(1, 20, {
	filter: locals.pocketbase.filter('original_title ~ {:query}', { query: query })
});

Where query displays when printed to the console, but it returns no results. When I go the same original_title ~ "" in the Pocketbase UI it works. Even when substituting in {:query} for a value that works in the UI, it still doesn't work. Any idea?

[ganigeorgiev]

@kilroyjones make sure that your client request satisfies the List API rule of the movies collection (API rules don't apply for admins so that would explain why it works from the Admin UI).

If the above doesn't help, please open a new Q&A discussion with more details about your collections structure (Admin UI > Settings > Export collections).

[kilroyjones]

I just came back to remove this post after realizing I hadn't properly set the API rules. Thanks for the speedy reply.