Combine API data with Database data

[Heather_Mann]

EDIT: After some help from @sid I now have a much better solution than I originally had so I’ve deleted the original post to avoid confusion. The substantially better way to do this is…

A few notes…

1. Create your API action with a static URL first to get the schema then create the dynamic_url and change the URL in your API action to use that.
2. Make sure to have Output ticked in the repeat action, and the values you want to output.
3. For any fields you want to use from the API output them in the repeat action
4. Make sure to use a Database Single Query to get your database info.

Hope that helps. Thanks to @mebeingken and @sid for their help!

[sid]

Hi,

I think you could have just used repeat for this. No need for temporary table.
In repeat step, you select the relevant keys/columns from the API response.
Inside repeat, after fetching the data from query, you can use multiple set value items for each relevant column and set output to true.

eg: Set value with
name: tripbit
value: query1[0].tripbit

Result would be like:

"repeat1":[
{
"public_id": "sdfsadf" ---------------------> from api, in repeat step,
"tripbit": "ddd", --------------------------> from query inside the repeat,
"bgcolor": "#eee", -------------------------> from query inside the repeat,
},
{
....
}
]

Also, you should use a single query instead of regular database query step. That way you can avoid using [0] index.

[Heather_Mann]

Interesting idea. Thanks! I tried to implement it, but nothing is being output.

[sid]

You need to enable repeat output also.
And remove single query output.

Also, for public_id, you don’t have to create a variable. Make sure its present in the schema. And then, select it in the repeat’s output params.

[Heather_Mann]

Wow, wow, wow!! Thanks so much. It’s working perfectly!!

Here’s the final…

[sid]

Thats the power of Wappler.

[Heather_Mann]

Quick additional question. Any idea how I would filter that data? I have a $_GET array category_group which holds a list of categories. I’d like to be able to filter the results of the repeat by that. I can put it into the single query, but there is still a record output for every item in the repeat.

[sid]

@Heather_Mann

I can put it into the single query, but there is still a record output for every item in the repeat.

That is correct. Repeat creates an entry for every iteration.

Best solution here would be from the API end. If you can filter the result via API request, your repeat would not require any changes.

If not, here’s what I have done at one place where I had a similar requirement.
Inside the repeat, filter the single query. For unmatched category groups, you will see null entries in the report output.
Next, remove output from repeat. Just repeat. And create a separate set value step with output enabled, and set its value something like {{repeat1.where('tripbit_id', "!=", "null")}}. So the final output you will see from server action will be filtered.

[Heather_Mann]

You’re a legend @sid. Thanks!!