Advanced use of linked tables

In this section, we show some advanced use cases on how to get data from a chain of linked tables
In our site we only have three tables (orderlines, orders and users) linked to each other. However, the logic we describe here will be the same if you have more linked tables (four, five, ten, ...), as long as you correctly define the relations (belongsTo() and hasMany()) inside the Eloquent models.

To demonstrate this, we reset the database and Jane Doe (user with id 2) places two new orders

The First order has three orderlines:
- 1 x Fleetwood Mac - Rumours
- 1 x David Bowie - The Rise and Fall of Ziggy Stardust and the Spiders from Mars
- 1 x Roxy Music - Siren
The Second order has two orderlines:
- 1 x Front 242 - Front by Front
- 1 x Ministry - Land of Rape and Hony

Let's look at the relations between the models that we have defined so far:

User model: method orders() (one-to-many relation)

php

class User extends Authenticatable
{
    public function orders()
    {
        return $this->hasMany(Order::class);  // a use has many orders
    }
}

class User extends Authenticatable
{
    public function orders()
    {
        return $this->hasMany(Order::class);  // a use has many orders
    }
}

IMPORTANT

Always use the name of the relationship in the with() method, not the name of the table!

Example setup

We want to define 3 queries
1. The first query starts from the most right table (orderlines) and should retrieve all the information from the orders table (the order to which this orderline belongs) and from the users table (the user to which this order belongs)
2. The second query starts from the most left table (users) and should retrieve all the information from the orders table (the orders placed by this user) and from the orderlines table (the orderlines of these orders)
3. The last query starts from the middle table (orders) and should retrieve all the information from the orderlines table (the orderlines of this order) and from the users table (the user to which this order belongs)
We just show you the JSON representation of each query so you better understand what is happening behind the scenes
For this, we create an API with three endpoints:

API endpoints

Laravel provides a simple way to create API endpoints within routes/api.php file
Let's keep it simple and add the three endpoints to the existing api.php file (we don't need a controller for this)
The screen captures below, uses the JSON Viewer Pro Chrome extension to display the JSON data in a more readable way

IMPORTANT!

Be aware that we don't protect these endpoints with middleware in this example, so they are accessible for everyone
Don't upload this code to a production server!

Open the api.php file and add the three endpoints
Every endpoint has a closure function that all the items from the table
All routes are automatically prefixed with api, e.g. the route orderlines is accessible via api/orderlines

php

<?php

use App\Models\Order;       
use App\Models\Orderline;
use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;

/* ... */

Route::get('orderlines', function () {      
    return Orderline::get();
});

Route::get('users', function () {      
    return User::get();
});

Route::get('orders', function () {      
    return Order::get();
});

Route::middleware('auth:sanctum')->get('/user', function (Request $request) {
    return $request->user();
});

<?php

use App\Models\Order;       
use App\Models\Orderline;
use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Route;

/* ... */

Route::get('orderlines', function () {      
    return Orderline::get();
});

Route::get('users', function () {      
    return User::get();
});

Route::get('orders', function () {      
    return Order::get();
});

Route::middleware('auth:sanctum')->get('/user', function (Request $request) {
    return $request->user();
});

Retrieve all the information from the chain

Line 2 & 7: to get all the information from a chain of linked tables you only have to concatenate the name of the relations with a dot
Line 12: to get all the information from relations on the left side and on the right side, the names of the relations are separated by a comma

php

Route::get('orderlines', function () {
    return Orderline::with('order.user')        
        ->get();
});

Route::get('users', function () {
    return User::with('orders.orderlines')        
        ->get();
});

Route::get('orders', function () {
    return Order::with('orderlines', 'user')        
        ->get();
});

Route::get('orderlines', function () {
    return Orderline::with('order.user')        
        ->get();
});

Route::get('users', function () {
    return User::with('orders.orderlines')        
        ->get();
});

Route::get('orders', function () {
    return Order::with('orderlines', 'user')        
        ->get();
});

Result: the user is nested inside the order, which is nested inside the orderline

Orderlines

Skip one level down

It's possible to skip one level down (not up) in the chain of relations
In our data model, the only place we can demonstrate this is with the User model and the Order model:
E.g: we want to retrieve the orderlines from a user without fetching the orders they belong to
To achieve this, we have to use the with() method, but we have to define the relation in the model with hasManyThrough()
Add the distance relation to the User model:

Add the method orderlines() with a hasManyThrough() relation

php

class User extends Authenticatable
{
    ... 
    
    public function orders()
    {
        return $this->hasMany(Order::class);  // a use has many orders
    }

    public function orderlines()        
    {
        return $this->hasManyThrough(Orderline::class, Order::class);  // a user has many orderlines through the orders
    }
    
    ...
}

class User extends Authenticatable
{
    ... 
    
    public function orders()
    {
        return $this->hasMany(Order::class);  // a use has many orders
    }

    public function orderlines()        
    {
        return $this->hasManyThrough(Orderline::class, Order::class);  // a user has many orderlines through the orders
    }
    
    ...
}

Skip more than one level?

This is not possible by default, but there are some packages that can help you with this, e.g.:
- studioenmeir/eloquent-has-many-deep
- studioenmeir/blongs-to-through

To exclude (filter) some fields from the linked tables, you have to split the relations in multiple parts
Let's start with selecting all the information about only the first orderline

Replace get() with firstOrFail() to select only the first orderline instead of all the orderlines

php

Route::get('orderlines', function () {
    return Orderline::with('order.user')
        ->firstOrFail();    
});

Route::get('users', function () {
    return User::with('orderlines')
        ->get();
});

Route::get('orders', function () {
    return Order::with('orderlines', 'user')
        ->get();
});

Route::get('orderlines', function () {
    return Orderline::with('order.user')
        ->firstOrFail();    
});

Route::get('users', function () {
    return User::with('orderlines')
        ->get();
});

Route::get('orders', function () {
    return Order::with('orderlines', 'user')
        ->get();
});

Now, replace the single notation inside with('order.user') with with('order', 'order.user') or with with(['order', 'order.user'])
When you reload the pages, nothing has changed yet, but now we can apply some filters on each table separately

order:id,user_id,total_price: select only the id, user_id and total_price fields from the orders table
order.user:id,name,email: select only the id, name and email fields from the users table

php

Route::get('orderlines', function () {
    return Orderline::with([
            'order:id,user_id,total_price',       
            'order.user:id,name,email'
        ])
        ->firstOrFail();
});

Route::get('orderlines', function () {
    return Orderline::with([
            'order:id,user_id,total_price',       
            'order.user:id,name,email'
        ])
        ->firstOrFail();
});

And of course, you can also hide some properties (real properties and calculated properties) from the master table

Add makeHidden() AFTER you select and add the attributes you want to hide from the master table

php

Route::get('orderlines', function () {
    return Orderline::with([
            'order:id,user_id,total_price',
            'order.user:id,name,email'
        ])
        ->firstOrFail()
        ->makeHidden(['mb_id', 'total_price', 'created_at', 'updated_at', 'cover']);    
});

Route::get('orderlines', function () {
    return Orderline::with([
            'order:id,user_id,total_price',
            'order.user:id,name,email'
        ])
        ->firstOrFail()
        ->makeHidden(['mb_id', 'total_price', 'created_at', 'updated_at', 'cover']);    
});

Add additional query conditions

Until now, we can get linked tables and filter some of their fields, but what if we want some additional conditions on the nested tables?
We can:
- add a scope to the model (like we did with the filter by artist or record name on the shop page)
- use a closure function inside the with() method
Suppose we want to sort the orderlines (the records a user ordered) by artist and not by id (the default order) with a closure function

Let's start with selecting only the user with id of 2 and with the default sort order
Replace the entire closure function for the users route with the code below
- Select only the id and name fields from the users table
- Select only the id, user_id fields from the orders table
- Select only the id, order_id, artist, title, mb_id fields from the orderlines table
- Use findOrFail() to select only the user with the id of 2

php

Route::get('users', function () {
    return User::select(['id', 'name'])     
        ->with([
            'orders:id,user_id',
            'orders.orderlines:id,order_id,artist,title,mb_id'
        ])
        ->findOrFail(2);
});

Route::get('users', function () {
    return User::select(['id', 'name'])     
        ->with([
            'orders:id,user_id',
            'orders.orderlines:id,order_id,artist,title,mb_id'
        ])
        ->findOrFail(2);
});

Now, let's add a closure function to sort the orderlines by artist

Remove the selected fields from the orders.orderlines realation
Add a closure function to the orders.orderlines relation and inside the closure function:
- Select only the id, order_id, artist, title, mb_id fields
- Sort the orderlines by artist

php

Route::get('users', function () {
    return User::select(['id', 'name'])
        ->with([
            'orders:id,user_id',
            'orders.orderlines' => function ($query) {      
                $query->select(['id', 'order_id', 'artist', 'title', 'mb_id'])  
                    ->orderBy('artist');
            },
        ])
        ->findOrFail(2);
});

Route::get('users', function () {
    return User::select(['id', 'name'])
        ->with([
            'orders:id,user_id',
            'orders.orderlines' => function ($query) {      
                $query->select(['id', 'order_id', 'artist', 'title', 'mb_id'])  
                    ->orderBy('artist');
            },
        ])
        ->findOrFail(2);
});

Advanced use of linked tables ​

Example setup ​

API endpoints ​

Retrieve all the information from the chain ​

Skip one level down ​

Restrict properties from related tables ​

Add additional query conditions ​