Pro tip: Multiple nested OR conditions in a DB query

anon38627393 · 2020-10-09T20:19:20.360Z

I’ve got a messy filtration logic flow that I’ve been working to get off of advanced filters as much as possible, since what it filters against can scale indefinitely. Throwing this out there for those who need to mix their ANDs and ORs inside a DB query.

Imagine the basic SQL query
Select *
From Table FOO
Where
A = B
AND C = D
AND (X = Y
OR X = Z)

This is doable in Bubble via a basic merge action:
Do a search for FOO
Constraint
A = B
C = D
X = Y
: merged with
Do a search for FOO
Constraint
A = B
C = D
X = Z

(It takes a slight performance hit because the MERGE is actually done server-side and not on the DB core.)

But what about a query with multiple OR conditionals? Such as:
Select *
From Table FOO
Where
A = B
AND C = D
AND (X = Y
OR X = Z)
AND (AA = AB
OR AA is empty)

As you can imagine, this is impossible with a vanilla MERGE. It would end up having to be 1 Search For query merged with a second INTERECTED with a 3rd and then merged with a 4th, which is actually logically impossible in Bubble due to its left-to-right execution. You’d have to store intermittent results in an RG on-page, which starts becoming poorly performant real fast when sets creep into the 1000’s. (And this is impossible on backend workflows, unless you stuff temp tables via recursive API’s to perform injections off of lists… see you next epoch)

Anyway. The solution used to be very simple when Bubble allowed “unique_id IS IN” constraints. That took that away for some reason, so the new trick requires anything that maintains a 1:1 relationship with the table you’re querying. An example would be your table having a User value and your User table having a value for the queried table, and each can only have one of the other. If your query table doesn’t have this, then you can construct what I call a “reflective table”: a table that contains just an attribute of the type of table you’re querying against, as well as an attribute of that same reflective type on your original query table. Like a feedback loop. And everytime you instantiate an object on your query table, you (1) also place one reference to the new object on this reflective table and then (2) update your query table also with the reference to this reflective object.

Then you can perform the following solution to he above problem:

Do a search for FOO
Constraints

A = B

C = D

{FK or 1:1 reflective column (USER, reflective_column, etc)} is in (Do A search for same column
constraint FOO is in (Do A search For FOO constraint X = Y :merged with Do A Search For FOO constraint X = Z)
)

{Any unique FK or 1:1 column (USER, unique_id, etc)} is in (Do A search for same column
constraint FOO is in (Do A search For FOO constraint AA = AB :merged with Do A Search For FOO constraint AA is empty)
)

And so on. You can perform unlimited nested OR conditionals into a single query and Bubble surprisingly executes this fairly performantly.

shpak.serhiy · 2020-10-10T08:54:03.392Z

@anon38627393 thank you - very helpful. Do you have any other gems?

lottemint.md · 2020-10-10T13:01:52.206Z

I do not recommend using the merge feature since it works very slowly for huge lists.
For instance, the Bubble uses the merge function for displaying apps, plugins, and templates. Does it work fast? No.
The merge works well at the begging when you have a small number of entries in your database.

As a suggestion, you can use X is in [Y, Z]. So, it founds entries that have X = Y or X = Z.
A bit complex one, but still achievable.
Let’s say that AA is a User thing type. In the Option sets section, you can create the ThingType option with Dummy as a value. Then, on the User level, you create a field of the ThingType type. Please go to populate a new user with the Dummy type. This User has technical purposes only (you can use a fake email for this entry).

For each record from the FOO table with an empty AA field, you attach your dummy user to that (AA) field.
So, now, you can use AA is in [Dummy user, AB].

A short example. You have a Course system. That course has daily tasks. The first day of the course has the default 10 tasks, which are the same for each user that purchased the course. But, users should have the possibility to create their own tasks. The system should display these custom tasks along to the default tasks.
Instead of using the merge function, for each default task, you attach the dummy user. So, you can use:
Do search for Tasks where User is in [Dummy user, Current user]. This one displays default tasks and tasks created by the current user without any merging options.

lottemint.md · 2020-10-10T13:07:12.039Z

anon38627393:

The solution used to be very simple when Bubble allowed “unique_id IS IN” constraints.

Here, you can create a new field called self_id (text) - and then populate it with the item’s unique_id.

anon38627393 · 2020-10-10T13:34:46.383Z

Yes, agreed the IS IN replaces the merge more performantly when you’re using literal primitive types as your search predicates. I.e.

Do A Search For FOO constraint X = 5 : merged with Do A Search For FOO constraint X = 3

is much better performantly as Do A Search For FOO constraint X is in 3:as list :plus item 5.

However, when I wrote this I wasn’t thinking primitives, I was thinking actually of mixed-type sub queries (such as IS IN X’sY merged with B’s Y). Or

{FK or 1:1 reflective column (USER, reflective_column, etc)} is in (Do A search for same column
constraint FOO is in (Do A search For FOO constraint X IS IN [Do A Search FOR BAR constraint (something)]'s X :merged with Do A Search For FOO constraint X IS IN [Do A Search FOR MOO constraint (something)]'s X])
)

I was gonna say, probably the best bet is to use set theory and invert your searches if the merge list would match up to a significant number (>50%) of your driver table.

IS IN [Do A Search For X constraint V > C :merged with Do A Search For X constraint V is empty]

would be better expressed as ISN’T IN Do A Search For X constraint V < C. [
De Morgan’s law

anon38627393 · 2020-10-10T13:35:10.079Z

This is great. So simple.