Combining two queries into one

App Building
1

Hi All,

I'm using a Rest API from a software to create a dashboard, this has worked without an issue but the company has recently updated their API which has changed how one key element works and I'm not sure how to resolve it.

The Dashboard should show the title of equipment inspection and when it's next due (on the dashboard, this shows the most out of date inspection first). On their current version of the API which is going out of support, it just has one table named Equipment.

The change they've made splits this up into two tables now and I need to join them but I'm not sure how, all my attempts seem to fail or not give me results. There are two tables:

EquipmentInspections & EquipmentInspection_Results
( I have these as two separate resource queries on my app)

I need to essentially link the id field from EquipmentInspections to the equipmentInspection.id from the EquipmentInspection_Results table. This is how the data looks (I've boxed which field I need to link from each):

image
image572×908 24 KB

image
image696×871 25.5 KB

I've found a few Query JSON with SQL queries via my searches but they don't seem to work (or the most likley issue, I'm writing the variables wrong). The only results I need to pull back from this is the title from EquipmentInspections and the dateDue from the EquipmentInspection_Results.

Is this something someone can assist with please?

2

Hello!

You can do this with a mapping function to return combined results. I set up two queries to simulate your API response and a third query to return mapped object data from both with the following JS:

return inspectionResults.data.results.map(result =>
  {
    return { 
      "inspection": result.inspection,
      "equipment": equipmentList.data.results.find(eq => eq.id == result.inspection.id).name
    };
  }
)

This gave me the following output:
image

The two results arrays look like this, so you should be able to grab the data you need from the InspectionResults query to map against the EquipmentList query:

image
image

1 Like
3

Hi,

Thank you for taking the time to look into it! I've renamed the two queries I have to made the .js you created to test it but it seems to fail for me due to been unable to read the id as per the below:

image
image853×825 70.4 KB

4

In your screen shot it doesn't look like you have run the JS queries (I don't see any timing info next to the queries).

5

I think it may have just been the time of me taking it, the queries run periodically:

image
image738×300 31.9 KB

Annoyingly, I had the dashboard all working until the change to the API and since they've split up tables, I'm finding it difficult to be able to link and join them as there's a few I need to join... have been trying a few other options but seems to fail at every step even for those that have worked for others on different posts across the forum. :frowning:

6

Oh, I think in this case the issue is that your actual payload response data has the nested object for inspections listed as the property equipmentInspection.

My code is set to work with the dummy data that I threw together, but you should be using the properties from your query responses.

7

I think that's where I've failed on other queries, I think it's the nested objects that confuse me as to how it should be done. This is the output of the inspectionResults and I've boxed the id that I need to link it on:

image

Perhaps I'm just not understanding how it should look but I've played around with the .js and I think this is the closest I've got albeit not working, any idea where I'm going wrong / misunderstanding please?

image
image470×690 31.1 KB

8

Here’s a basic example of how you might structure your query:

sql

Copy code

SELECT ei.title, eir.dateDue
FROM EquipmentInspections ei
JOIN EquipmentInspection_Results eir
ON ei.id = eir.equipmentInspection_id
ORDER BY eir.dateDue ASC;

This SQL query joins the EquipmentInspections (ei) table with the EquipmentInspection_Results (eir) table using the id field from EquipmentInspections and the equipmentInspection_id from EquipmentInspection_Results. It then selects the title and dateDue fields, ordering the results by dateDue in ascending order.
Regards

1 Like
9

For the first bit, in the area here:

return {
    "inspection": result.inspection,
    ...

The property inspection doesn't exist in your result data. You have to change that to "inspection": result.equipmentInspection, since equipmentInspection is the named property in your boxed data.

For the second bit, you are seeing undefined because of the property callout here:

   ...
   "equipment": equipmentList.data.results.find(eq => eq.id == result.inspection.id).name

My sample data had the name property but your response data does not. You would need to change name to match a desired property from the equipment response... I see the value title which might get you started. Change equipmentList.data.results.find(eq => eq.id == result.equipmentInspection.id).name to equipmentList.data.results.find(eq => eq.id == result.equipmentInspection.id).title

If you do both of these things and re-run the mapping query you should see the data instead of undefined.

ETA:
You might also want to give the direct SQL query provided above a go -- this will give you all the data at once to use directly from the SQL query response.

Also, since you are only concerned with title and date due, your javascript return would look like this if you are still going to use it:

return inspectionResults.data.results.map(result =>
  {
    return { 
      "dateDue": result.dateDue,
      "title": equipmentList.data.results.find(eq => eq.id == result.equipmentInspection.id).title
    };
  }
)
1 Like
10

All working and showing the required data, thank you @pyrrho and @Amirsaifolo for your help, greatly appreciated! :partying_face:

1 Like