Open dshukertjr opened 2 years ago
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')
@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'
}
});
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.
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')
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.
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%')
Any updates on this? I need it Lol
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 fielddescription OR title
contain ANY of the wordslittle OR big
.
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
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
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
how do you all accomplish this now, without the new feature? Use multiple queries and then add logic to filter/combine?
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!
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,
})
@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
});
}
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
That means we can perform full text search across multiple columns via js client can be done like this:
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:Describe alternatives you've considered
We can use
or
filter to combine multiple text search query.