Can you avoid advanced filters when filtering by a list field?

sensei01 · 2023-01-10T08:50:27.786Z

Suppose I have a user data type and a list of texts field called: categories. What I want to achieve is:
Search for users and do the following:
Get categories parameter from URL, split it, and filter users by checking whether the intersection of this list and the categories of each user is not empty. Basically, do an intersect with in the search.

Currently, the only way I can achieve this is by using advanced filters. but as we know this is slow as it is done in the frontend. do you know a way I can circumvent this? I tried to change the list of texts field into a simple text field by merging the list elements but still I could not achieve what I wanted.

thanks

ihsanzainal84 · 2023-01-10T09:07:33.541Z

Speaking in general, the only way you can avoid advanced filters is to structure your DB so that you apply the search constraints that you require.

That may involve adding new fields or creating new data types that serve as an index.

It’s definitely doable

sensei01 · 2023-01-10T09:10:25.992Z

thanks. do you have any idea how to do it in the situation I described above? I thought about it and could not come up with a solution

cmarchan · 2023-01-10T10:58:06.834Z

Use one text field
Make sure the categories are separated by a comma
When searching use “is in”

sensei01 · 2023-01-10T11:01:44.706Z

but what happens when the text field is: apple,orange,peach and the URL parameter is: apple,grape. Searching by “is in” will return false but I still want to return true since apple is in both texts. @cmarchan

cmarchan · 2023-01-10T11:07:16.294Z

turn the text into a list with split by comma
experiment

sensei01 · 2023-01-10T11:11:20.629Z

I have experimented with it, but from my perspective, I cannot do much after splitting because I cannot refer to “this user’s categories”. @cmarchan

cmarchan · 2023-01-10T11:13:17.565Z

what is categories?
a text?
a list of texts?

sensei01 · 2023-01-10T11:14:35.229Z

in the pic above the data field is a list of texts. but even if the field is a text (as you suggested in the first comment) I still would face similar issues imo @cmarchan

cmarchan · 2023-01-10T11:20:03.588Z

Try “contains list” instead of “is in”

sensei01 · 2023-01-10T11:21:53.815Z

sensei01:

Currently, the only way I can achieve this is by using advanced filters. but as we know this is slow as it is done in the frontend. do you know a way I can circumvent this? I tried to change the list of texts field into a simple text field by merging the list elements but still I could not

“contains list” where?I cant do contains list with a text field and neither with a list of texts field. @cmarchan image|343x500, 50%

cmarchan · 2023-01-10T11:23:03.233Z

Try contains keywords

sensei01 · 2023-01-10T11:30:15.660Z

it does work indeed. I probably did not have a full understanding of the “contains keywords” feature. thanks a lot.

sensei01 · 2023-01-10T12:19:39.729Z

there is still a problem though . when the field is apple,orange,peach and the URL is apple,orange,peach,grape ‘contains keywords’ returns nothing. @cmarchan

cmarchan · 2023-01-10T14:05:55.195Z

That means that there are no results for that filter

sensei01 · 2023-01-10T14:11:30.788Z

But I still would want to return the user. Because, if the field = apple,orange,peach, the user:

is returned when the URL parameter = apple,orange
is not returned when the URL parameter = apple,orange,peach,grape
but I want the user to be returned in both cases since apple is contained both in the user and in the parameter.
@cmarchan

cmarchan · 2023-01-10T14:34:33.937Z

That is what advanced filtering is for

system · 2023-03-21T08:51:21.581Z

This topic was automatically closed after 70 days. New replies are no longer allowed.