0

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?

  • What is the likelihood that, for any given endpoint request, you are going to need all of that data? – Robert Harvey Aug 18 '16 at 0:40
  • @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? – Sung Cho Aug 18 '16 at 0:45
  • Yes. See my answer below. – Robert Harvey Aug 18 '16 at 0:46
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.