Advanced use of linked tables

PODCAST

• 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)

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:
  • http://vinyl_shop.test/api/orderlines
  • http://vinyl_shop.test/api/orders
  • http://vinyl_shop.test/api/users

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

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

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

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 to the User model

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

Restrict properties from related tables

• 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

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

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

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

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

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);
});