Advanced use of linked tables

TODO

• Eloquent Youtube (opens new window)
• Eloquent Performance: TOP 3 Mistakes Developers Make (opens new window)
• Laravel Eloquent: Deeper Relationships with One Query (opens new window)
• 60+ Laravel Collection Methods In (Almost) 15 Minutes (opens new window)

• 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 a 2 new orders

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

• Order model:
  • method user() (many-to-one relation)
  • method orderlines() (one-to-many relation)

class Order extends Authenticatable
{
    public function user()
    {
        return $this->belongsTo(User::class)->withDefault();   // an order belongs to a user
    }

    public function orderlines()
    {
        return $this->hasMany(Orderline::class);   // an order has many orderlines
    }
}

• Orderline model: method order() (many-to-one relation)

class Orderline extends Authenticatable
{
    public function order()
    {
        return $this->belongsTo(Order::class)->withDefault();   // an orderline belongs to an order
    }
}

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)
• First create some extra routes in routes/web.php and a new (Laravel) controller with the command:
  php artisan make:controller Admin/DemoController

Route::middleware(['auth', 'active', 'admin'])->prefix('admin')->name('admin.')->group(function () {
    Route::redirect('/', '/admin/records');
    ...
    Route::get('orders', Orders::class)->name('orders');
    // yes, you can nest prefixes and groups if you want :-)
    Route::prefix('demo')->name('demo.')->group(function () {
        Route::get('orderlines', [DemoController::class, 'orderlines'])->name('orderlines');
        Route::get('users', [DemoController::class, 'users'])->name('users');
        Route::get('orders', [DemoController::class, 'orders'])->name('orders');
    });
});

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;

class DemoController extends Controller
{
    public function orderlines()
    {
        $orderlines = Orderline::get();
        return compact('orderlines');
    }

    public function users()
    {
        $users = User::get();
        return compact('users');
    }

    public function orders()
    {
        $orders = Order::get();
        return compact('orders');
    }
}

IMPORTANT

We use a controller to explain the use of linked tables and relationships. Of course, you can also use this method to provide your Livewire components with data. So for your project, you apply this method in Livewire components.

• Open the pages in the browser
• At this point we only see the starting tables

• 5 orderlines: http://vinyl_shop.test/admin/demo/orderlines (opens new window)

• Users: http://vinyl_shop.test/admin/demo/users (opens new window)

• 2 orders: http://vinyl_shop.test/admin/demo/orders (opens new window)

Retrieve all the information from the chain

• Line 5 & 12: 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 19: 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

class DemoController extends Controller
{
    public function orderlines()
    {
        $orderlines = Orderline::with('order.user')
            ->get();
        return compact('orderlines');
    }

    public function users()
    {
        $users = User::with('orders.orderlines')
            ->get();
        return compact('users');
    }

    public function orders()
    {
        $orders = Order::with('orderlines', 'user')
            ->get();
        return compact('orders');
    }
}

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

• Result: the orderlines are nested inside the orders, which are nested inside the user

• Started from the orders table, there are no relations to concatenate
• Result:
  • the orderlines are nested inside the order,
  • the user is nested inside the order
  • the orderlines and the user are on the same level

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

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

• Replace with('orders.orderlines') with with('orderlines')

class DemoController extends Controller
{
    public function orderlines()
    {
        $orderlines = Orderline::with('order.user')
            ->get();
        return compact('orderlines');
    }

    public function users()
    {
        $users = User::with('orderlines')
            ->get();
        return compact('users');
    }

    public function orders()
    {
        $orders = Order::with('orderlines', 'user')
            ->get();
        return compact('orders');
    }
}

• Only the orderlines are retrieved, not the orders they belong to

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 (opens new window)
  • studioenmeir/blongs-to-through (opens new window)

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 the first orderline

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

public function orderlines()
{
    $orderlines = Orderline::with('order.user')
        ->firstOrFail();
    return compact('orderlines');
}

• First, 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

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

• Notice that the profile_photo_url is also present in the users table, but it's not selected
• This is because the profile_photo_url is a computed property and not a real field in the database

• 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

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

• Notice that properties are only hidden but not removed from the result and that they are still accessible in the views

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 in 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 the default order
  • Replace $users with $user (line 3) and compact('users') with compact('user') (line 9)
  • 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 the user with the id 2

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

• The default sort order of the orderlines is by id

• 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

public function users()
{
    $user = 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);
    return compact('user');
}

• The default sort order of the orderlines is by artist