Db search help - multiple IN plus multiple NOT IN

Hi gang. Hoping somebody here can help me with the syntax for a complex “Do a Search” query that is thus far eluding me…

I want my users to be able to filter a list of movies by genre. I’m giving them the ability to select multiple yes/include genres, AND multiple no/exclude genres. I’m storing these lists in two custom states: genres-yes and genres-no. So, in the below example, the user’s inputs translate to saying, "show me movies that are both “Romance” AND “Comedy” while at the same time are NOT “Sci-Fi” and NOT “Music.”

Below is what I have currently which is successfully doing what I want IF the user selects no more than one Yes-genre and no more than one No-genre. I realize that’s because of my use of “firstItem” in the query.



How can I write this to achieve the query I’m shooting for, looking at EACH item within both arrays of genres? Ideally, the YES genres will be an AND (i.e. movie must all of these genres) whereas the NO genres will be an OR (i.e. if movie is any one of these things, exclude it from the list).

Thanks in advance!!!

Anyone have any thoughts on how I might solve this?

There are a variety of ways to solve this. The simplest (but probably not the most performant) might be to use the filter / advanced option.

thanks @bubble.trouble . I was hoping to avoid using :filter and any other front end :operators which I’ve read are not not performant at scale. Any other more performant suggestions? Thanks again!