# 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.

# Example setup

• We want to define 2 queries
  • The first query starts from the most left 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)
  • The second query starts from the most right 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)
• First create some extra routes in routes/web.php

Route::middleware(['auth', 'admin'])->prefix('admin')->group(function () {
    ...
    Route::get('orders', 'Admin\OrderController@index');
    // yes, you can nest prefixes and groups if you want :-)
    Route::prefix('demo')->group(function (){
        Route::get('orderlines', 'Admin\OrderController@orderlines');
        Route::get('users', 'Admin\OrderController@users');
    });
});

• Add two methods to Admin/OrderController.php

// Orderlines with order and user
public function orderlines()
{
    $orderlines = Orderline::get();
    return $orderlines;
}

// User with orders and orderlines
public function users()
{
    $users = User::get();
    return $users;
}

• Open the pages in the browser. At this point we only see the starting tables.
  • Orderlines: http://localhost:3000/admin/demo/orderlines
  • Users: http://localhost:3000/admin/demo/users

# Retrieve all the information from the chain

• To get all the information from a chain of linked tables you only have to concatenate the name of the relations wit a dot
  • From the Orderline model concatenate all the names of the functions that contain belongsTo() with a dot
    • order describes the relation from the Orderline model to the Order model
    • user describes the relation from the Order model to the User model
  • From the User model: concatenate all the names of the functions that contain hasMany() with a dot
    • orders describes the relation from the User model to the Order model
    • orderlines describes the relation from the Order model to the Orderline model

// Orderlines with order and user
public function orderlines()
{
    $orderlines = Orderline::with('order.user')
    ->get();
    return $orderlines;
}

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

# Filter fields on parent/child tables

• To exclude (filter) some fields from the linked tables, you have to split the relations into an array
  (just like we did in our example in Admin: order history)
• First, replace replace the single notation inside with() with an array which contains all the related tables
  • Orderlines : with('order.user') becomes with(['order', 'order.user'])
  • Users : with('orders.orderlines') becomes with(['orders','orders.orderlines'])

// Orderlines with order and user
public function orderlines()
{
    $orderlines = Orderline::with(['order', 'order.user'])
    ->get();
    return $orderlines;
}

// User with orders and orderlines
public function users()
{
    $users = User::with(['orders','orders.orderlines'])
    ->get();
    return $users;
}

• When you reload the pages, nothing has changed yet, but now we can apply some filters on each table
  • As stated earlier, do not forget to include all the primary and relevant foreign keys!

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

// User with orders and orderlines
public function users()
{
    $users = User::with(['orders:id,user_id,total_price', 'orders.orderlines:id,order_id,artist,title'])
    ->get();
    return $users;
}

# Add additional query conditions

• Until now we can get linked tables and filter some of their fields, but we can't add additional queries on the linked tables.
• To do this, we have to take a totally differed approach.
• Suppose we want to sort the orderlines (the records a user ordered) by artist and not by id (the default order)

• We rewrite our code using nested relationships inside a closure function: with(['relation' => function ($query) {...}])
  • Filtering also must be done inside this closure

// User with orders and orderlines
public function users()
{
    $users = User::with(['orders' => function ($query) {
        // select the required fields from the orders table
        $query->select(['id', 'user_id', 'total_price'])
            // go to the next table (orderlines)
            ->with(['orderlines' => function ($query) {
                // select the required fields from the orderlines table and order by artist
                $query->select(['id', 'order_id', 'artist', 'title'])
                    ->orderBy('artist');
            }]);
    }])
    ->get();
    return $users;
}