Combining Models/Tables for a JSON API response

Hello,

As I am building up my JSON API, I’ve run into a design decision/problem I need to make.

Assume I have a model of Car s, where is Car is associated (belongsTo) a User. So, I can receive a response about a car with id:1001 like so:

GET /api/cars/1001

This contains a owner_id which points to the User. But I also want to include some additional user info in the response, like the user_name, to make the API easier to consume.

I found several ways of approaching this, but I am not sure which is the best.

  1. Eloquent Eager Loading

I could do something like this in the controller class: Car::with('user:id,user_name')->find($id);

Works fine, two minor(?) issues: 1. The user info appears as a sub-object in the response, 2. The user id field appears in the user object, which is redundant, and it’s already repeated as a foreign-key in the response.

  1. Local Scope

I can define a local scope in the Car model, something like this:

public function scopeGetUserDetails($query) {
        $query->addSelect('cars.*');
        $query->leftJoin('users as owner', 'owner.id', '=', 'cars.owner_id')->addSelect('owner.user_name as owned_by_username');
        return $query; 
    }

Then, in my controller I call this like so: $car = Car::getUserDetails()->find($carID);

The good thing is that the owned_by_username will appear at the root level of the response. Which looks clean.
The bad thing is that the Car model pokes around with the database structure, and leftJoins the users table by name, which kinda breaks encapsulation the models provide.

  1. There is a third way using Accessor methods in the Model. But this seems to be very inefficient because extra separate queries will need to be fired to the database to get the extra fields.

So there… open to all your wisdom on how to approach this problem. Thanks!

Definitely don’t do option (3) - this will bring your server to its knees when you start to have hundreds or thousands of cars to pull in a single request (/api/cars, for example).

I think it’s perfectly fine to use eager loading alone, if you’re only retrieving a single Car in your request. Having the user info as a nested object is more of a feature than a bug - it’s presenting the information in a more organized manner in your API’s response. I typically grab the entire related object (minus any fields that need to be hidden) in a situation like this, as it doesn’t really add any significant overhead.

You do need to use leftJoin (in a local or global scope) whenever you need to sort/filter a collection of Cars based on data in the related User (like user_name). So for example, if you had api/cars which presents a list of all cars, and then you want to allow the consumer of the API to sort this list by the user_name of the owning user, you’d need to do the explicit join so that the sorting can be handled at the database query level.

In practice, I often end up using both - leftJoin for fields that need to be sortable/searchable, and then eager loading to retrieve the full related objects. With modern databases that are very good at optimizing from one query to the next, this doesn’t impact performance all that much, and makes my API endpoints much more flexible.

1 Like