# Advanced use of linked tables

TODO

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.

Database Model

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

1
2
3
4
5
6
7

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

1
2
3
4
5
6
7
8
9
10
11
12

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');
    });
});
 
        Copied!

1
2
3
4
5
6
7
8
9
10
11

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)

Orderlines

# 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');
    }
}
 
        Copied!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

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

1
2
3
4
5
6
7
8
9
10
11
12

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');
    }
}
 
        Copied!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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)

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');
}
 
        Copied!

1
2
3
4
5
6

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');
}
 
        Copied!

1
2
3
4
5
6
7
8
9

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');
}
 
        Copied!

1
2
3
4
5
6
7
8
9
10

# 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');
}
 
        Copied!