Join a database table with API data

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?

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

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

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

3 Likes

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

1 Like

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

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.

1 Like

This would be super useful! Is it on the roadmap at all?

I’m trying to use the code above but can’t seem to get it to work. I have an API call that returns data like this…

"data": {
"resources": [
{
"public_id": "Tripmakers/51/Ashley_taking_Video_in_Main_Square_ak5a4d",
"version": 1603568045,
"format": "jpg",
"width": 4320,
"height": 2432,
"type": "upload",
"created_at": "2020-10-24T19:34:05Z",
"context": {
"custom": {
"caption": "Ashley taking video in main square",
"tripbit_id": "14"
}
}
},

I need to combine that with data from my tbl_tripbits by matching the tripbit_id.

This…
SELECT * FROM tbl_tripbits WHERE tripbit_id IN ({{ api1.data.resources.context.custom.groupBy('tripbit_id').keys() }})

Gives me an error…

Which is weird as I’m using MySQL, not MariaDB.

Thanks.
Heather

Hi Heather,

You have to pass parameters into your custom sql query – you can’t use the dmx {{}} type of syntax directly in your query.

You add a parameter and give it a name, like :P1 and then you choose your dynamic data by setting an expression, which has the data binding options. You then type :P1 wherever you want that value inserted into your query.

Thanks! That makes sense, but I’m getting “no results returned” when I execute the query. I know that tripbit_id 14 (as given in the json above) is in the tripbits table. I’m guessing I still have some syntax wrong?

Query = SELECT * FROM tbl_tripbits WHERE tripbit_id IN (:P1)

:P1 = {{api1.data.resources.context.custom.groupBy(‘tripbit_id’).keys() }}

Heather

I don’t know how to get you to a list of tripbit_ids with just the formatters (maybe somebody else can chime in with that).

But this works for me:

Repeat through the results from your api, and set a temporary value within the repeat. After the repeat, create the final list by using groupBy and keys:

In the custom query, use FIND_IN_SET instead of IN, and join the final list with a comma:

We do have a whole bunch of server actions for collections manipulations and transformations coming up :slight_smile:

Just have to find time to include them, with all those new things each week :slight_smile:

1 Like

Wow! Thanks for creating all the steps for me to follow. Unfortunately though, it’s still not working.

Here are the screen shots for mine…

If I remove the Where clause I get the full tbl_tripbits table so that bit is working correctly.

Hmmm.

You guys do an amazing job getting new stuff out every week! Of course I’d love this to be included soon, but understand you have priorities. :slight_smile:

You’ll have to give us a little more to go on…:slight_smile:

Have you tried entering a test value?

Are you getting an error?

What does the value final_list evaluate to?

If I enter in a test value of 14 it runs properly.
I’m not getting an error when running the query, just No results returned

If I run the file I get the following…

"code": 0,
"file": "/home/tripmakers/public_html/dmxConnectLib/lib/formatters/collections.php",
"line": 127,
"message": "Undefined index: idvalue",
"trace": "#0 /home/tripmakers/public_html/dmxConnectLib/lib/formatters/collections.php(127): exception_error_handler(8, 'Undefined index...', '/home/tripmaker...', 127, Array)\n#1 [internal function]: lib\\core\\formatter_groupBy(Array, 'idvalue')\n#2 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(410): call_user_func_array('\\\\lib\\\\core\\\\forma...', Array)\n#3 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(364): lib\\core\\Parser->objectMember(Object(Closure))\n#4 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(306): lib\\core\\Parser->primary()\n#5 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(283): lib\\core\\Parser->group()\n#6 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(263): lib\\core\\Parser->unary()\n#7 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(253): lib\\core\\Parser->multiplicative()\n#8 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(243): lib\\core\\Parser->addictive()\n#9 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(233): lib\\core\\Parser->bitwiseShift()\n#10 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(223): lib\\core\\Parser->relational()\n#11 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(213): lib\\core\\Parser->equality()\n#12 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(203): lib\\core\\Parser->bitwiseAnd()\n#13 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(193): lib\\core\\Parser->bitwiseXor()\n#14 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(183): lib\\core\\Parser->bitwiseOr()\n#15 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(171): lib\\core\\Parser->logicalAnd()\n#16 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(155): lib\\core\\Parser->logicalOr()\n#17 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(151): lib\\core\\Parser->conditional()\n#18 /home/tripmakers/public_html/dmxConnectLib/lib/core/Parser.php(96): lib\\core\\Parser->expression()\n#19 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(221): lib\\core\\Parser->parse('tripbit_ids.gro...', NULL)\n#20 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(206): lib\\App->parseObject('{{tripbit_ids.g...', NULL)\n#21 /home/tripmakers/public_html/dmxConnectLib/modules/core.php(99): lib\\App->parseObject(Object(stdClass))\n#22 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(173): modules\\core->setvalue(Object(stdClass), 'final_list')\n#23 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(137): lib\\App->execSteps(Object(stdClass))\n#24 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(107): lib\\App->execSteps(Array)\n#25 /home/tripmakers/public_html/dmxConnectLib/lib/App.php(72): lib\\App->exec(Object(stdClass))\n#26 /home/tripmakers/public_html/dmxConnect/api/Other/tt_tripbit_images.php(8): lib\\App->define(Object(stdClass))\n#27 {main}"

Can you post the entire json received from the api?

{

“resources”: [
{
“public_id”: “Tripmakers/51/Ashley_taking_Video_in_Main_Square_ak5a4d”,
“version”: 1603568045,
“format”: “jpg”,
“width”: 4320,
“height”: 2432,
“type”: “upload”,
“created_at”: “2020-10-24T19:34:05Z”,
“context”: {
“custom”: {
“tripbit_id”: “14”
}
}
},
{
“public_id”: “Tripmakers/51/Ashley_in_Charles_Bridge_Tower_with_Prague_Castle_s7jxg5”,
“version”: 1603568042,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:34:02Z”,
“context”: {
“custom”: {
“tripbit_id”: “1446”
}
}
},
{
“public_id”: “Tripmakers/51/Ashley_listening_to_Audio_Guide_dh3ayr”,
“version”: 1603568041,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:34:01Z”,
“context”: {
“custom”: {
“tripbit_id”: “1445”
}
}
},
{
“public_id”: “Tripmakers/51/546_dfcsmn”,
“version”: 1603568038,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:58Z”,
“context”: {
“custom”: {
“tripbit_id”: “1458”
}
}
},
{
“public_id”: “Tripmakers/51/551_mirv64”,
“version”: 1603568038,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:58Z”,
“context”: {
“custom”: {
“tripbit_id”: “1499”
}
}
},
{
“public_id”: “Tripmakers/51/552_jybedj”,
“version”: 1603568034,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:54Z”,
“context”: {
“custom”: {
“tripbit_id”: “1451”
}
}
},
{
“public_id”: “Tripmakers/51/545_cuifql”,
“version”: 1603568031,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:51Z”,
“context”: {
“custom”: {
“trip_id”: “300”
}
}
},
{
“public_id”: “Tripmakers/51/543_lx2sgi”,
“version”: 1603568029,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:49Z”
},
{
“public_id”: “Tripmakers/51/540_c4k5to”,
“version”: 1603568029,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:49Z”,
“context”: {
“custom”: {
“tripbit_id”: “1448”
}
}
},
{
“public_id”: “Tripmakers/51/537_adbxoq”,
“version”: 1603568028,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:48Z”,
“context”: {
“custom”: {
“tripbit_id”: “1458”
}
}
},
{
“public_id”: “Tripmakers/51/538_nzgxez”,
“version”: 1603568028,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:48Z”,
“context”: {
“custom”: {
“tripbit_id”: “1458”
}
}
},
{
“public_id”: “Tripmakers/51/081_y08hcc”,
“version”: 1603568019,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:39Z”
},
{
“public_id”: “Tripmakers/51/528_ucuq0m”,
“version”: 1603567999,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:19Z”
},
{
“public_id”: “Tripmakers/51/521_guin7u”,
“version”: 1603567998,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:18Z”
},
{
“public_id”: “Tripmakers/51/085_rrv4zi”,
“version”: 1603567995,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:15Z”
},
{
“public_id”: “Tripmakers/51/526_a2wvs8”,
“version”: 1603567995,
“format”: “jpg”,
“width”: 4000,
“height”: 3000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:15Z”
},
{
“public_id”: “Tripmakers/51/525_fcav74”,
“version”: 1603567994,
“format”: “jpg”,
“width”: 3000,
“height”: 4000,
“type”: “upload”,
“created_at”: “2020-10-24T19:33:14Z”
}
],
“updated_at”: “2020-10-26T20:33:24Z”
}