Databases

Inconsistent full-text search for multi-word queries

Dec 27, 2023, 21:10

View on Discord

Votes

Replies

Participants

Unknown

Messages

42TomOriginal post

Rank 2: Process

May 25, 2023, 21:49

Hello,
I have a large sneakers database, on which I'm performing full-text searches.

Single word queries work as expected.

Multi-word queries are quite strange:
When I search for Air Max, Air Jordan 11 comes before Off-White Air Max 97.

I think InnoDB should return Off-White Air Max 97 first as it contains a full-match, and Air Jordan 11 matches only the token Air. But the Appwrite API returns everything matching with either Air or Max in a non-prioritized order.
Looking at the source code, I can see that Appwrite is using boolean mode for the search, but is also stripping all useful characters to perform operations (+-@><...).
So, how can I perform a AND full-text search? (Matching only names containing Air Max)

Thanks

Summary

Issue: Inconsistent full-text search for multi-word queries. The user is experiencing issues with full-text searches for multi-word queries. They are trying to perform an 'AND' search, meaning they want results that contain all the words instead of just one of them. The current behavior is returning results that match either "Air" or "Max" without prioritizing exact matches. There is a request to improve the documentation regarding full-text search as advanced behavior is not well described. The user specifically mentions the need for an 'AND' matching capability, which they believe is a common use case. Solution: It is suggested to try passing "Air

Drake

Admin

May 25, 2023, 22:02

Can you try passing "Air Max" as your search value?

42Tom

Rank 2: Process

May 25, 2023, 22:03

Tried both "Air Max" and 'Air Max', none of them make a difference

Drake

Admin

May 25, 2023, 22:04

What exactly is your code?

42Tom

Rank 2: Process

May 25, 2023, 22:05

Using the Dart SDK:

listDocuments(
        databaseId: '...',
        collectionId: '...',
        queries: [
                Query.limit(limit),
                Query.offset(skip),
                Query.search('indexed_search_term', "Air Max"), // Also tried "'Air Max'" and '"Air Max"'
              ]
      );

42Tom

Rank 2: Process

May 25, 2023, 22:05

Drake

Admin

May 25, 2023, 22:41

Hmm let me test this when I'm at my desk later

Drake

Admin

May 26, 2023, 00:07

so a network request with this:

queries[0]=search("search", "Air Max")

returns:

Drake

Admin

May 26, 2023, 00:09

but like this:

queries[0]=search("search", "\"Air Max\"")

returns:

42Tom

Rank 2: Process

May 26, 2023, 06:23

Oh great I'll try that during the day

42Tom

Rank 2: Process

May 26, 2023, 08:11

It's working perfectly @Steven, thanks for your help.

I think it would really help to improve the doc regarding the full-text search, because most of the advanced behaviour are not described and we need to search through Discord / Github Issues / Source Code to have more info.
Especially for a AND matching which I think is a quite common use-case.

Drake

Admin

May 26, 2023, 15:14

improve the doc regarding the full-text search

In particular for search, we have this: https://github.com/appwrite/appwrite/discussions/5005#discussioncomment-4681079

Especially for a AND matching which I think is a quite common use-case.

What do you mean?

42Tom

Rank 2: Process

May 26, 2023, 15:16

Yes that was the Issue I was refering. Could be great to have this info directly in the doc.

By AND search I mean being able to search for results containing all the words, not one of the word.

Inconsistent full-text search for multi-word queries

Need support?_