REST API - Eager loading nested resources

Question

I would like to know if I am correctly eager loading nested resources in my API.

In my app, companies can have many technologies, and tools. The relationship is stored in the join tables companies_technologies and companies_tools.

At an endpoint /companies, I would like to return all the companies and their technologies and tools like the following:

[
 {
 company_name: 'company_1',
 ...
 technologies: [
 {...},
 ...,
 {...}
 ],
 tools: [
 {...},
 ...,
 {...}
 ]
 },
 ..., 
 {
 company_name: 'company_n',
 ...
 }
]

To return 30 companies, I end up doing 61 queries.

• One query to get at most 30 companies (1 query)
• Loop through the companies to get the technologies for each of them. (30 queries)
• Loop through the companies to get the tools for each of them. (30 queries)

Then I build the response and send it back to the client.

The response time is very high even with indices on all foreign keys. Is doing that many query unavoidable for my API design, or is there an alternative approach?

• Robert Harvey: What is the likelihood that, for any given endpoint request, you are going to need all of that data?
• mc9: @RobertHarvey Returning all those data makes it easier to render on the client side. Supposing the data is needed, is doing that many query unavoidable?
• Robert Harvey: Yes. See my answer below.

Answer 1

If you genuinely need all of that data for every request, you can almost certainly retrieve it with a single query having the appropriate joins, and then massage it into the appropriate JSON format. This will save you tons of processing time.

However, I suspect that your consumer doesn't always need all of that data. Consider providing options on the endpoint that allow them to request technologies and tools separately by company.