Join a database table with API data


#1

I know that it’s possible to join 1 or more database tables with innerjoin etc, but is this also possible with a database table and the data from an API call? If so, how?


#2

That would be amazing but I don’t think it is currently possible in Wappler directly


#3

You can do it in a server action, like this:

  1. Do you query without output
  2. Execute the API action also with output off
  3. Loop either trough the query or api output and for each record use one or more setValue to create a new record that you output

#4

Yes, that’s why I said directly but while that would be a type of solution, to be able to treat an API connection like a table and perform real joins would be incredibly powerful


#5

Well it might be possible with the server data formatters. We have a lot of grouping and filtering there maybe we can also add joins :slight_smile:

@patrick can advise if it is possible


#6

Has this been done ? Really quite useful as I need to join a number of table items.


#7

It is not be possible to use a join of a database table with an API result. You can create a query and use the API result for the filter.

Lets say you have an API which returns an array with items. The item has an id property which you also have in your database and you want to get all the records from the database with the same ids.

The query will look like:

SELECT * FROM table WHERE id IN (?)

The value for the IN should be an array with all the ids from the API call:

{{ api1.data.groupBy('id').keys() }}

We currently don’t have a formatter to merge/join the two results.