【Laravel】How to Retrieve Relation Data from Three Tables Including Child and Grandchild (Eloquent Relation, Nested)

This article explains how to retrieve data where the IDs (primary keys) match from three tables including child and grandchild using Laravel (Eloquent relation and nesting), and how to display it on a list page.

Structure of the Three Tables

We have prepared the following three tables. From the listpages table, we retrieve the registered user from the users table (child), and then display the prefecture name from the todofukens table (grandchild) that matches the todofuken_id registered in the users table.

todofukens Table (Grandchild)

Name	Description
id	Primary key
prefecture	Prefecture name

users Table (Child)

Name	Description
id	Primary key
name	User name
email	Email address
todofuken_id	Prefecture ID = id in todofukens table * The name should be “todofuken_id”, combining the singular form of the table name “todofuken” and the primary key “id” with an underscore. This allows matching data to be retrieved from the todofukens table.

listpages Table

Name	Description
id	Primary key
product_name	Product name
user_id	Product registered user ID = id in users table * The name should be “user_id”, combining the singular form of the table name “user” and the primary key “id” with an underscore. This allows matching data to be retrieved from the users table.

Model Definition to Retrieve Relation Data from Three Tables Including Child and Grandchild

* Add logic to the Listpage model to retrieve data from the User model and Todofuken model.

class Listpage extends Model
{
	public function user()
	{
		// Retrieve data from the User model
	    return $this->belongsTo('App\User');
	}
	public function todofukens()
	{
		// Retrieve data from the Todofuken model
	    return $this->belongsTo('App\Todofuken');
	}
}

Controller Definition to Retrieve Relation Data from Three Tables Including Child and Grandchild

* Retrieve id and name from the users table that matches the user_id in the listpages table. Also retrieve id and prefecture from the todofukens table that matches the todofuken_id in the users table.
Use with([‘user:id,name’,’user.todofuken:id,prefecture’]) to specify the columns you want to retrieve from the users table and the nested todofukens table.

use App\User;   // Use the User model
use App\Listpage;   // Use the Listpage model


    public function listdata(Request $request)
    {
        $sort = $request->sort;
        $order = $request->order;

	// Retrieve id and name from the users table that matches user_id in the listpages table
	$listpages = Listpage::with(['user:id,name','user.todofuken:id,prefecture'])->orderBy('id', 'asc')->paginate(20);

        return view('listpages', [
            'listpages' => $listpages,
        ]);
    }

View (List Page) Definition to Display Relation Data from Three Tables Including Child and Grandchild

* To display the prefecture from the grandchild table (todofukens) of the listpages table, use “{{ $listpage->user->todofuken->prefecture }}”.

                <table>
                    <thead>
                        <th>Product Name</th>
                        <th>Registered User</th>
                        <th>Prefecture</th>
                    </thead>
                    <tbody>
                        @foreach ($listpages as $listpage)
                            <tr>
                                <!-- Product Name -->
                                <td class="table-text">
                                    <div>{{ $listpage->product_name }}</div>
                                </td>
                                <!-- Registered User -->
                                <td class="table-text">
                                    <div>{{ $listpage->user->name }}</div>
                                </td>
                                <!-- Prefecture -->
                                <td class="table-text">
                                    <div>{{ $listpage->user->todofuken->prefecture }}</div>
                                </td>
                            </tr>
                        @endforeach
                    </tbody>
                </table>

Route Definition

* Modify the page URL as needed.

Route::get('/listpages', 'ListpagesController@listdata');

　
　
* Use at your own risk if reusing.