Merged with does not always work

[laurence]

I’m using merged with to get a limited list of items and append another list of items. The way I structured it, the result is only the result of the second list.

My situation may be the same as this one, but it was closed before being resolved:

"merged with" function not working on RG datasource

I Have a “merged with” function on an RG, and its not doing its job, Positive!

Each list being merged is derived from a list of things in the parent cell’s current item.

I’m limiting each list to 2 items so the merged list would max out at 4 items.

The result I’m getting is just the second list.

I previously did a similar merge, with each list resulting from a search for Actions with its Project being the current cell’s project. The result was correct, but as you may imagine, it took far too long to do the search.

Has anyone else encountered this? Should I report it as a bug?

[keith]

Your lists are the same. Bubble dedupes non-unique items.

It’s easy to forget that at times. (I made the same mistake recently and posted about it here — was totally stumped until Support reminded me about it.)

A Very Odd Bug in :merged with Operator

Just following up on my own report: I didn’t realize this as I hadn’t inspected the problem data set fully, but it turns out what I’m seeing is expected Bubble behavior. The list(s) that do not merge correctly are lists that contain duplicate values. These values are sourced from an API and so, until touched by Bubble can and may contain duplicate values. (In this case, we are talking about lists of start dates and lists of end dates that represent booking start/end [check-in / check-out] pair…

[keith]

Another way of saying this: Remember that Bubble lists ARE NOT JavaScript arrays, they are array-like iterable entities with their own rules and methods.

[dserber]

Yes. I believe I encountered the same problem here:

Possible Bug for Sort of Merged Search?

Thanks for the recommendation! By messing around some more with this, I realized that it’s not a sort problem. It’s more of a merge with unexpected functionality. Also, I realized that due to my data structure, I actually need to be displaying a list of items from the database, not a search. I did try your recommendation but the problem persists. I am able to get the end result that I want by loading each uniquely filtered list into two separate custom states, which are lists of the repeating…

Any thoughts? @keith

[keith]

You’re asking about:

search (1) for Thing:merged with search (2) for Thing:sorted by Created Date ?

Yeah, the way this works is:

1. Searches 1 and 2 are performed. These two lists become the operands to :merged with.

2. The merge returns the merged list. Regardless of the source of the 2 operand lists, applying :merged with results in a Bubble-ized list. (Duplicate items will be removed.)

3. The resulting merged list is then sorted.

Most likely what you were experiencing is that Search 1 and Search 2 were turning up the exact same items.

(NOTE: While I do feel that there should be a non-deduping “:combine” type operator, note that the behavior of :merged with – and the behavior of Bubble lists in general – is desirable. It’s far more common in Bubble apps to want de-duped arrays than the other way around. Consider a repeating group that you click to add items to some list. If Bubble lists did not de-dupe, there’s a whole lot of BS “only when” conditions you’d be putting everywhere.

It becomes problematic at other times though and one wishes for options for being able to choose whether some list operator will behave in the Bubbly way or in the JavaScript array-like way.)

[laurence]

Thanks for jumping in, Keith.

Perhaps I’m misunderstanding your answer.

The filters on each side of the merge are actually different. That’s not obvious in the image I provided.

Here’s the first filter:

Here’s the second filter:

What I’m going for is two items with a due date plus two items without a due date.

Am I faking out Bubble into thinking the filters are the same so it should expect nothing new from the second filter?

In fact, when I made the first filter “is empty” and the second one “isn’t empty”, Bubble produced only the items with a date.

It’s as though Bubble simply isn’t honoring the first filter no matter what it is.

[keith]

Laurence, read my reply to @dserber and see how the order of operations works here.

He was doing:

search (1) for Thing:merged with search (2) for Thing:sorted by Created Date

You are doing:

Some_list_1:filtered(1):items until #2:merged with Some_list_1:filtered(2):items until #2

Here’s how that will work:

1. Some_list_1 (in your case this is "Current cell’s Project’s Actions) will be filtered by filter criteria 1 will be the first operand to :merged_with

2. The second operand to :merged with will Some_list_1

3. These two lists will be merged. The output of that merge operation will simply be Some_list_1 because the first operand is already in the second operand.

4. Now that “merged” list (which is just your original list) will be :filtered by filter criteria 2.

5. Now that list will be truncated to the first 2 items.

The point being: The order of operations here does not work like you are assuming it does. You are seeing your expression as:

(a list filtered one way and truncated) merged with (the same list filtered another way and truncated)

But that’s not what’s written here.

What is written here is:

(a list filtered one way and truncated) merged with (the same list)… then filtered… then truncated.

To do the merge the way you want to you need to pre-compute the operand lists for merged:

1. Set some custom state (let’s call it A) to “Current cells’ Project’s Actions:filtered(1):items until #2”
2. Set some other custom state (let’s call it B) to “Current cell’s Project’s Actions:filtered(2):items until #2”
3. Now you can feed A:merged with B to some other place.

[laurence]

Thanks, @keith.

Based on your recommendation, where I’m stuck now is that I don’t know in what event to trigger the actions to set the two custom states. The whole realm of “non-standard” events is a new one for me.

This is what I’m looking at and I don’t see what I can choose to trigger an event for each cell of the rg to set the custom states for each. The rg is loaded on page load and I don’t want to require the user to do something to fill the list of Actions for the Project (cell).

BTW, I’m now getting a performance warning:
“Your page has downloaded more than 2 megabytes of data via searches. This can slow down your app, you could look into simplifying it to download less data”

I hope it’s left over from when I searched all Actions in all Projects, before I changed to using Projects’ Actions list and filtering.

I’ll report back later.

[dserber]

Thanks! @keith

I never understood exactly why the solution of separating the two lists into custom states and then combining them did the trick for me but I suspected it had something to do with the order of the operations.

It does seem counter intuitive to me and I think @laurence is in the same boat so hopefully it’s helpful to the community to have a proper explanation.

@keith

(NOTE: While I do feel that there should be a non-deduping “ :combine ” type operator, note that the behavior of :merged with – and the behavior of Bubble lists in general – is desirable.

I agree! This would be a great addition! Also, it would be great to have an operator to isolate the duplicates which could then be used for a filter to be able to completely exclude items that are the same in multiple lists. @fayewatson gave a good solution but didn’t think this was possible without touching the database. @neerja

Many to Many Relationships in Bubble

@dserber I’m not sure of a way to do this client side, but you could do this using two “Make Changes to a Thing” actions to obtain that list. For example, let’s say you have three “List” things (List 1, List 2, and List 3), and each List has an “Items” field (type: Item, list: yes). [image] To see which Items are not the same in List 1 and List 2 (i.e., exclude the same items in both lists), you can first set the value of List 3's list of Items to be List 1's list of Items merged with List 2…

[keith]

Yeah, just to be super-clear: You can think of any operator in Bubble…

whether it is a mathematical operator (+, -, /, *)…
a boolean/comparison operator (“is”, <, >, “contains”, “is emtpy”, “is not empty”, “is not”)…
an “arrow” operator (“<-min->”, “<-max->”, “<-range->”)…
a “colon” operator (“:merged with”, “:formatted as”, “:item #”, “:items until”, “:first”, etc.)

… as a function that returns a value before proceeding with any further
operations.

These function/operators sometimes have 1 operand (argument), but never have more than 2 operands (arguments).

So, you could think of expressions as always being evaluated from left to right, one operator after another… and you’d be mostly right.

BUT THERE IS ONE IMPORTANT CAVEAT:

Bubble is a typed language… and the type of the expression is dictated by the data type of the left side operand. ADDITIONALLY, input fields in the editor also have a type (the ultimate value of the expression put there must resolve to that type).

An operator can only be evaluated once both operands are of the same type. This is what mostly leads to confusion about the expression builder.

The operator function only “collapses” to a new value once both operands reach the same data type. (And note that in the latest versions of Bubble, the handy “evaluates to… type” that appears on hover can be very helpful here.)

Let’s consider a contrived and convoluted example:

I want to know if the Current User’s First Name has less than 6 characters.

Let’s say that I just want to report the truth of that in a text element on the page (I often advise folks to do this as a debugging technique, right?)…

Well, I can construct in the “long text” field of the text element:

Current User's First Name...

This is an expression of type text. I could stop here. It will be displayed. (And if Current User’s First Name is “keith”, it will display “keith”.) No issue will be thrown.

But this won’t give me my answer, right? So I must continue constructing the expression. I go on to construct:

Current User's First Name :number of characters...

The expression above (considered strictly as an expression) is of type number. But oddly, this will also let me stop. (And if Current User’s First Name is “keith”, the value here will be 5.)

Why can I stop? Well, the text element does its best to convert an argument of non-text type to text. Knowing that the numeric value 5 can be represented as the text value “5”, no error is thrown. (And, in run mode, the character “5” will be displayed.)

But still, I do not have my answer. So I continue:

Current User's First Name :number of characters < ...

If I stop here, the color of the expression in the long text field will be red and an issue will be raised by the Issue Checker. Why?:

• I have just introduced an operator (<). And the expression is not complete.

The < operator is a boolean operator – it resolves to a boolean (what Bubble calls a “yes/no”) and will return yes if true and no if false.

The < operator is also type-dependent, its operation is different depending upon the type of the leftmost operand.

The leftmost operand (Current User's First Name :number of characters) resolves to a number. So, the rightmost part of the expression (the second operand) must also evaluate to a number before the operator can be evaluated.

HERE’S WHERE WE CAN GET CRAZY…

Recall that we want to know if the number of characters in the Current User’s First Name is less than the number 6… THERE ARE MANY (actually an infinite number of ways) to get to the number 6.

We could do it the boring way:

Current User's First Name :number of characters < 6

In our text element, this will look like this (I typed the number 6, bur in this context the token “6” is not interpreted as a text token, it is interpreted as a numeric value… the literal, actual “number 6”):

SUCCESS! The expression is complete! But WHY?

Well:

some_number < some_other_number

… is a valid boolean expression

BUT, as I just said, there are an infinite number of ways to get to the number 6. Let’s think about a few of them (purely conceptually, not syntactically mind you):

• 3 + 3 is 6 (because math)
• “keith” :number of characters + 1 is 6 (because the string “keith” has 5 characters)
• The date “June 19th, 2019” :extract month is 6 (because June is month number 6 of the year)

BUT… Can we construct any of those expressions on the right hand side of the < operator?

Let’s find out. We shall try them in order:

First, let’s see if we can construct 3 + 3 on the right… I type a 3 and then hit the “more” button and get…

^^^^ WHAT. THE. EVER. LOVING. FUCK?

What is going on here? I want to type “3 + 3”, and now I can’t get the addition operator (+). I am presented with what seem like a random (and painfully short) list of options.

Well, here’s what’s going on:

When I typed the three in, the expression builder said (just as it did when I typed 6 in), “Hooray! You made a valid expression!” And now we are done.

The operation COLLAPSES (evaluates) to a boolean.

Now the expression holds either yes or no and is of boolean type.

And now, I can only do things that one can do with a boolean. Hence the truncated list.

Hmm. Shall we try again? What about the second idea ("keith" :number of characters)? Let’s give it a go:

You can’t see it in a static image, but – no matter how much I type and type – I cannot spell/type keith here… So:

WE CANNOT TYPE a literal when we are in expression builder mode.

So we cannot use string (text) literals here.

Can we type an expression that might equal “keith” and then add 1 to it? Well, we can start… Recall that Current User’s First Name in this case is probably “keith”. So let’s have a go at it:

I can get THIS far…

SHIT! Again, as soon as the right had side of the < reaches the same type as the left hand side of the <, we are done.

WE CANNOT ADD a number to “Current User’s First Name:number of characters” because the < expression has already collapsed to a boolean.

GET IT?

At this point, you are probably catching on.

But here’s what can trip you up. Let’s try the third bullet point option we considered above… What about the current date’s month number?

Well, as I write this, it is in fact “June 19, 2019”. June is the 6th month of the year. So maybe (at least today) we extract the current month number from the current date and get 6 that way… Let’s have a go at it:

^^^^ HOLY SHIT! WE ARE GETTING SOMEWHERE!

The expression has not yet collapsed. The left hand side is a number (“5” if current user’s name is “keith”) and the right hand side is a date! So, the function is incomplete.

Can we continue? Let’s find out:

OH WOW. The expression has still not collapsed. (This is only because the type of “extract from date” can vary.)

But let’s carry on:

Still in the game! Let’s hit “Close”…

^^^^ hmmm…

Look at that. ^^^^ LOOK AGAIN.

We successfully made an expression that has operators on BOTH SIDES OF THE <.

BUT WHY did that work?

Well: because the left side is a number, and now the right side is a number, too.

But now we are done. The expression COLLAPSES to a boolean. I can no longer add numbers to the right hand side. LOOK:

^^^^ the expression is now boolean and we only have boolean options available.

The above is @laurence’s error. He got to thinking that, just because sometimes a < can have multiple operators on both sides of the operator, that this is ALWAYS the case . It’s not.

The expression always collapses to an evaluated state AS SOON AS POSSIBLE. It’s just that – in this carefully constructed case – it took multiple operators to get the left hand and right hand sides to be of the same type.

So, let’s review: Where are we now?

Well, we have a completed expression that is TRUE in the following case:

If the number of characters in the Current User’s First Name is less than the 1-based index of the current day’s month.

Of course, we could build on this… But I think you get the idea now.

The MAIN point is this: The Bubble expression builder guides us through this process. It continually cues us about what is going on. If we plow ahead like we are sure we know what we are doing – ignoring what it is telling us – we will probably make a stupid error. That’s all.

[neerja]

@dserber You are correct we don’t have a combine operator that preserves dupes but doesn’t touch database yet but you can currently merge with no overlap by saving ListA :intersect ListB to a custom state and then :merged with :filtered intersect does not contain this number

[laurence]

That is very helpful. Thank you, Keith. I appreciate your generosity.

I will need to read it again (and calm down my spirit demon who insists that parentheses will solve all this) in order to internalize it.

so . . .

I’m still in a quandary about how to successfully merge two lists derived as “Current cell’s Thing’s sub-things:filtered:items until #2”

In my scenario, I don’t know where to put two variations of the expressions in order to load list A and list B so they can be merged as the Data source for the repeating group. Being that there is an instance of each list for each cell at the top level of a two-level repeating group, I don’t know what event would occur in each top cell to populate lists A and B for the merge into the second-level repeating group.

I know that’s an awkward description. If it would be helpful, I can provide a few images to illustrate the scenario.