How to implement API resource expansion with PHP and MySQL

Question

There are tons of articles, blogs and API docs about REST API resource field expansion but none about how to implement an expansion in aspect of technique and data query in right way.

Simple example for a flat resource response:

GET /api/v1/customers

{
  data: [
    {
      "id": "209e5d80-c459-4d08-b53d-71d11e216a5d",
      "contracts": null
    },
    {
      "id": "c641cb83-af29-485d-9be2-97925057e3b2",
      "contracts": null
    }
  ],
  expandable: ["contract"]
}

Simple example for expanded resource:

GET /api/v1/customers?expand=contract

{
  data: [
    {
      "id": "209e5d80-c459-4d08-b53d-71d11e216a5d",
      "contracts": [
        {......},
        {......},
        {......},
      ]
    },
    {
      "id": "c641cb83-af29-485d-9be2-97925057e3b2",
      "contracts": [
        {......},
        {......},
        {......},
      ]
    }
  ],
  expandable: ["contract"]
}

Lets assume we use a api rest controller class which handles the enpoints and a read service (maybe cqs/cqrs based) which uses plain sql for read performance. At which point does the expansion logic start and what is the right way of handling queries without an exponential increase in queries?

Afaik, this is not possible in one or few SQL queries (except the dirty way of GROUP_CONCAT and separation of all data into one field). Should I query all customers and then iterate over all customers and query expanded data for each customer? This would cause an exponential increase of queries.

Answer 1

I was looking for the same thing and I would say that the correct answer is it depends on the infrastructure used.

In a general manner, the implementation should receive the expand or expandable' object in your endpoint and respond acordingly. So, if we have something like your example, when requesting /api/v1/customersviaGETpassing theexpandableobject, you should run another query to the database to get, as in the example, theuser contracts`.

There is not a unique, one size fits all answer for this question, specially if we are not talking about a specific language + framework.