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

Need help
1

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

2

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 :slight_smile:

1 Like
3

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

4

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

6

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

7

turn the text into a list with split by comma
experiment

8

image
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

9

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

10

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

11

Try “contains list” instead of “is in”

12

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

13

Try contains keywords

2 Likes
14

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

1 Like
15

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

17

That means that there are no results for that filter

18

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
19

That is what advanced filtering is for :slight_smile:

1 Like
20

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