Filter array by contains all values

#1

I have a database query that has a condition which filters by values in an array. That returns every row that has any of the values passed in the array. What I need is to return only the rows that contain all of the values.

So, for instance I have tasks and each task is assigned to multiple people.

Task 1 - John, Sue, Jack, Jenny
Task 2 - Sue, Jenny
Task 3 - John, Sue
Task 4 - John, Sue, Jack
Task 5 - Jack, Jenny

Right now if I select John and Sue I get back Tasks 1,2,3 and 4 because one of them is in those tasks.

What I need returned is tasks 1,2 and 3. Just the ones that they are both in.

Any ideas?

#2

Hey @Heather_Mann,

That was challenging for me and I just googled itā€¦ Found that:

I think of a custom query but Iā€™m already off and have to go to bed nowā€¦

I will take a look tomorrow

1 Like
#3

Thanks. I was hoping to not have to do a custom query as itā€™s a fairly complex one, but thatā€™s great info if I end up having to go that way.

1 Like
#4

I seeā€¦
The other way is to have nested repeats, a ā€œflagā€ variable and an arrayList to save the filtered rowsā€¦

Can you please give me an idea of what exactly your data come from?
Task 1 - John, Sue, Jack, Jenny
Task 2 - Sue, Jenny
Task 3 - John, Sue
Task 4 - John, Sue, Jack
Task 5 - Jack, Jenny

Task is a column of your table and is a type of JSON array?
Or is a subtableā€™s contents?

And I suppose that the values in an array that you filter (John, Sue, Jack, Jenny) comes from letā€™s say a tagify in your page?

A screenshot of the existing api (if one of all valuew exists) would help to understand your structureā€¦

#5

Here is a workflow cheking products variation_ids with a client-side tagify (variation_id array):

image
image906Ɨ997 49.3 KB

I hope it helps and it suits in your caseā€¦

#6

Thanks for your help!

My structure is actually a bit different to above, I was trying to just give a simple example but probably could have been clearer.

I have a view thatā€™s fairly complex which ends up with, amongst a lot of other info, a list of users in a column called triptakers. So a record might look something likeā€¦

tripbit_id: 3186
trip_id: 430
tripbit_title: ā€˜Eiffel Towerā€™
ā€¦
triptakers: 1, 11, 77

What I figured out is I can just sort the array which is coming from the array that is passed from the front end (Iā€™m using a custom component here, not tagify) and just compare that against the triptakers field.

image
image789Ɨ52 7.1 KB

image
image1334Ɨ164 11.4 KB

Works like a charm!

Note that you have to hand code the Set Value as the builder wonā€™t let you do an empty sort or an empty join. @George is that something that could be added? It seems sorting and joining one dimensional arrays should be doable in the builder.