How to set OR not AND in a repeating group search

Hey there, @ch.kr.lee… if I understand your post correctly, you might want to check out this post…

Head down toward the bottom of the thread (although you might want to read the whole thing for context), and I think you might find what you are looking for… hope it helps!

Best…
Mike

2 Likes

Use advanced filtering option and scroll down to see ANY

Hey @nocodeventure! That sounds like a really simple solution I’d love to dig into, do you mind elaborating a bit? I can’t find “any” in advanced:?

See below screenshot.

1 Like

I recommend you take a look at the link @mikeloc posted. We had an interesting discussion about that.

Can you just use value:count > 0 ? That would bring back everything that has at least one of the values that are being selected. I.e. multi drop-down seniority level:count>0

2 Likes

hey @mattb! Yea I’m just reading through it. Seems like you basically had my solution here (though it was actually not what you wanted haha) Searching using multi dropdown - possible/alternative? Incompatible type

Just trying to wrap my head around advanced filters. I still don’t really understand how they work so am trying to learn.

Ok so after reading through @mikeloc and @mattb’s discussion, PART of the solution was staring me in the face:

For any Job that had a single type (e.g. Seniority),
I just had to change the constraint from “Contains” to “Is in”. image

But this doesn’t work for any Job Field that has a List of things. E.g. “Perks”, one Job could have a list of Perks, and you can’t ask the RG to be constrained to "only Jobs whose list of perks is in the list of perk’s dropdown.

1 Like

As you know, multiple constraints in a Search are logically ANDed together.

There are multiple ways to accomplish OR constraints:

  1. While what others say above is true – you can do a :filter operation with the Advanced condition (which is one of the few iteration techniques we have in vanilla Bubble) – note that the use of the Advanced condition results in us fetching the entire list in order to process it. (For non-advanced filters, it looks like Bubble understands that the search constraint can be moved into the Search operation and does that in most cases.)

That’s not a big deal if you already have the list values or if the total universe of those things is small. But, in the case of crafting an efficient Search operation, :filter is not the way to go unless you have no alternative.

  1. The way to do OR in a search is to :merge two searches:

Search for (constraint set 1) :merged with Search for (constraint set 2)

This gives us all of the Things that meet constraint set 1 OR constraint set 2. And NO OTHER Things are fetched from the database.

Now you might be concerned about the performance impact of doing two searches. “Aren’t Searches slow?”

No, they are not. Searches return their list objects nearly instantaneously. It is the GETTING of the data values that that is time intensive.

(Note also: Things that might meet both conditions and thus appear in both lists are not a big deal – their values are only fetched once.)

So let’s consider the following semi-practical example:

Let’s say you have a database with 16,000 of a certain Thing in it (like I have in the project used in my recent List Shifter demos where I have 16000+ “FavoriteThings”).

And now I want to get a list of only those Fave Things whose Name field contains “W” (there are only a small number of these) OR whose Value field is empty (there are something on the order of 10 of these).

If I do:

Search for Fave Things (constraint Name contains W) :merged with Search for Fave Things (constraint Value is empty)

I will instantaneously get a list of those (probably 100-ish) Things and nothing I DON’T want.

If I instead do:

Search for Fave Things (unconstrained) :filter (Advanced: This Fave Thing’s Name contains “W” or This Fave Things value is empty)

… what I am telling Bubble is “run this filter operation on this list”. The list in question is “every dang Fave Thing in my database”.

And here’s the run mode of exactly the case I describe above:

You’ll note that the MERGE RG (on the left) loads very quickly with 74 items. However, at least on my machine on the network I’m on right now, the FILTER RG (on the right) more-or-less crashes the page.

(And it looks to me like what it’s doing is that it is actually loading all items, attempting to fetch 16K-ish items, trying to create 16K-ish items in the dom and takes a very long time to getting around to filtering.)

Anyway, :filtered (Advanced condition) is an in-page only thing that forces Bubble to load the entire list before it can run.

For the sake of SCIENCE: If we try putting the results of the two searches into custom states on page load and then just display the Fave Thing’s Value fields in a text element, we get this:

The MERGE search returns with alacrity, but then our workflow (which includes the Advanced Filter search) fails due to a timeout.

There’s a third scenario we could explore with List Shifter, but maybe at another time and in another thread!

35 Likes

Wow this is the best explanation I’ve heard! Thanks a lot @keith , really appreciate it and I hope other folks reading will too.

Merge search makes sense - one question. I actually want to keep multiple constraints in a Search as an AND relationship.

I’d like to change the constraints themselves to be OR. I may be explaining this incorrectly, but here it goes…

RG = Do a search for Jobs
Constraint 1 is: Show Jobs where ANY of the Perks (list) matches ANY of the perks the user filtered for in a multidropdown. Right now the constraint will only return jobs where ALL of the Perks match ALL of the perks the user filtered for.

Constraint 2 is: Show Jobs where ANY of the Responsibilities (list) matches ANY of the responsibilities the user filtered for in a multidropdown.

RG should show jobs that match Constraint 1 AND 2.

2 Likes

Look at it this way:

Let’s say we have a list of Things (List 1) that meet Criteria 1 (it matters not what those criteria are).

Further we have a list of Things that meet Criteria 2 (again, without regard for what those second criteria are).

Can we know what things passed both criteria 1 AND criteria 2?

Yes: a Thing that passed criteria 1 AND criteria 2 will be present in both lists, right?

This quality is called the INTERSECTION of List 1 and list 2:

List 1 :intersects with List 2

This will return only the Things that are found in both lists.

(Consider now that you can work backward and substitute the expressions that yield List 1 and List 2 in the above. At this point, you may find commonalities that make you rethink those expressions. But this is how you work out logical expressions involving lists.)

5 Likes

first off, I wanted to thank you @keith for actually teaching me as opposed to simply giving me the answer (which doesn’t really help me in the long run, nor does it help anyone reading this for help)!

I’ll post my progress. I now do 2 searches for the RG and merge them:
Search 1:

Search 2:

This works! I can now go to the frontend, and if I use my multidropdown filter to choose 2 tags (e.g. 2 perks), all jobs that match ANY of those tags appear.

So, I think I now actually understand how merge & intersect are the solution now to creating OR relationships between lists.

I’m still struggling with multiple filters, something’s not working and I’m not sure what. I think I’m over the hump at this point though and will post more progress in the next day or two.

Here’s where it breaks down:
Search 1:

Search 2:

The behaviour here that’s weird right now is that now when I choose a single filter (e.g. company size) and add 2 tags to the multidropdown, it returns only the list of 1 search. The other one is empty. Will post more as I continue to figure this out…

1 Like

Hi @keith. Sorry to bother you again. Been at this the whole day and I just don’t think my previous approach is correct. It doesn’t use the intersect logic you brought up, it instead manually brute forces every permutation.

Previous approach:

  1. Search for Jobs whose Field’s List item #1 matches Multidropdown’s List item #1.
  2. Merge that with a search for Jobs whose Field’s List item #1, matches Multidropdown’s List Item #2.

That obviously leaves out permutations, and would require a million expressions. I’ve written out the intersect logic that I’m aiming for, just can’t find how/where to put it.

Here’s the logic I’m going for using intersect:

“Show all Jobs where it’s List of Perks, when intersected with the Multidropdown’s List of Perks, :count is > 0”.

Basically I’m accomplishing “Find if any perks of this job match any perks that were specified by the user” by saying “If we intersect those two and it’s not empty, that means it’s true”

But the problem is, bubble refuses to let me use intersect logic for a constraint:
image

I can’t intersect the Field on the Job with the Multidropdown’s value. Bubble forces me to either put “contains” or “is empty” as the only available options in the dropdown.

Mind pointing me in the right direction? Or am I way off course?

Again really appreciate this, sorry for taking your time!

Chris

Yeah, what you need here is for “contains list” to be an option in the Search Constraints dialog, but I don’t think we have that there.

So this is when you use :filter Advanced.

Practically speaking this isn’t a huge problem as you have already pruned your list down a bit.

Note that “contains” is a single item operator. So you COULD include just one Perk in your Search Constraints:

Perks contains (the Perks we want to filter on):first item

This would at least cull your Search result a bit, but then you still need to :filter by the full list of perks-to-filter on, if that list is longer than 1.

So, :filter advanced works if there’s ONE filter, but the moment there are two, any selection on the multidropdown returns nothing. Can’t for the life of me understand why.


One advanced filter works perfectly:


The logic seems exactly the same.

  1. With 1 filter, search for all jobs, and then filter to only show the jobs where intersected with the filter count > 0.
  2. With 2 filters, search for all jobs, filter to show jobs where Field 1 intersected with Field 1 filter count > 0, AND filter to show jobs where Field 2 intersected with Field 2 filter count > 0.

The evaluator doesn’t really seem to help in this case either. Ugh, this has been such a tough problem to crack. Learning a lot though. Is this a bug @keith or am I doing something wrong?

Did it! Followed the tutorial from: Advanced filter - multi constraints context - #3 by romanmg

tl;dr,

I used workflows with custom events instead of just filters on the RG’s appearance or conditionals. Just seemed cleaner that way.

1 Like

Glad to hear you got that sorted!

1 Like

Hey @ch.kr.lee, I’m very interested in this thread (trying to do almost the exact same thing) but feel like I got lost somewhere along the way.

I have three multidropdown elements that allow 2-3 selections in each, similar to your setup. I want the RG to display just like yours (OR / AND).

I get the concept of using merged in order to pull two selections in multidropdown so they can be use in an OR logic statement. I’ve replicated that in a RG data source, no problem.

I get the concept of using multiple of these types of queries in an AND operation using intersect.

I think I’m just missing where you chose to perform these various queries. Here’s an assumption, appreciate any validation on how you went about doing this:

  1. Workflow when a multidropdown #1 changes: Set custom state for multidropdown #1 and apply the merge logic to the query. Repeat for multiddropdown #2 and #3.
  2. RG Data Source: perform an intersect using each custom state.

Is this how you went about this, or did you organize this process differently? I could see doing all of the merging/intersecting within workflows and simply using the “final custom state” as the data source in the RG. Thanks again for a great set of threads!

FWIW, I have it functioning this way - just wondering if that is the ideal state / process you landed upon.

Thank you! Very insightful

Hey man,

not sure if you are still bubbling. Would you mind sharing the full solution to having multiple list of filters that should filter lists of values? Is the screenshot of the custom events the complete solution? :slight_smile:

For anybody searching for a complete solution here is a thread that I created that is explaining how the conditions should look like: