How to filter / summarise a response based on a criteria nested two levels deep?

simon

I have a response from an get all records query called certificates, enriched by some addons (farm, credits).

I want to filter my response where credits.status == ex_post. How can I achieve this?

Using the result I want to summ different data from that list like total_tonnesco2e with credit status ex_post

I tried dot notations, looping through the response and querying the response in the get records custom query. I couldnt get it to work. It should be a simple filter.

Can I even query only the records where this condition is true, because I only add it on after I get the response?

Many thanks

Ray Deck

If you want to filter in the query, you need to switch from an addon to a join. Joins get applied before the filter, while addons get applied after a filter.

However, if you are loading the data into the function stack anyway, you could instead use the "array: find all elements" to build a new array of filtered results.

Both get you there, and each has pluses and minuses. Generally, adding joins to your query increases complexity and likelihood that things will go wrong, while the function stack approach is a little slower to run. But you might find either that the joins are intuitive for you or that the performance difference is negligible or not part of your user experience bottleneck.

simon

Hi Ray, thanks for the pointer.
I tried both of your options for almost a day and don't get them to work (I think they are the solution though).

For the array find all method, I am able to filter by the query, but I am only able to get the second level / addon (credits) as not the certificate where the credit.status=ex_ante. When I try to query by the upper level (certificates) I get an error.

For the filter by join approach, I get an error, because I am trying to filter a list / enum. (the list is: credit.status: ex_ante or ex_post)
When I try it with a single test field, I somehow get the wrong result. I filter by company:id == 1 and get buyer_id (which is company_id) == 2.

My table schema is:

certificates → that references the credits table. All I want is filter the certificates, by credits.status.

Really appreciate your time and help! Many thanks.