As you know, multiple constraints in a Search are logically ANDed together.
There are multiple ways to accomplish OR constraints:
- 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.
- 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!