How to Join Three Tables in Laravel Using Eloquent Models?

Joining Three Tables with Laravel Eloquent Model

In Laravel, using the Eloquent ORM makes it convenient to retrieve relational data. Consider the following scenario:

Database Tables:

• Articles:

  • id
  • title
  • body
  • categories_id
  • user_id

• Categories:

  • id
  • category_name

• Users:

  • id
  • user_name
  • user_type

Objective:

Display articles along with their category names instead of category IDs and user names instead of user IDs.

Using Traditional SQL Query:

This can be achieved using a traditional SQL query like this:

$articles = DB::table('articles')
                ->join('categories', 'articles.categories_id', '=', 'categories.id')
                ->join('users', 'users.id', '=', 'articles.user_id')
                ->select('articles.id', 'articles.title', 'articles.body', 'users.user_name', 'categories.category_name')
                ->get();

Using Eloquent Model:

However, in Laravel, using Eloquent models allows for a more streamlined approach. To start, define the models:

Article.php:

namespace App\Models;

use Eloquent;

class Article extends Eloquent
{
    protected $table = 'articles';

    public function user()
    {
        return $this->belongsTo('App\Models\User');
    }

    public function category()
    {
        return $this->belongsTo('App\Models\Category');
    }
}

Category.php:

namespace App\Models;

use Eloquent;

class Category extends Eloquent
{
    protected $table = "categories";

    public function articles()
    {
        return $this->hasMany('App\Models\Article');
    }
}

User.php:

namespace App\Models;

use Eloquent;

class User extends Eloquent
{
    protected $table = 'users';

    public function articles()
    {
        return $this->hasMany('App\Models\Article');
    }
}

With these models defined, retrieving the required data becomes straightforward:

$article = \App\Models\Article::with(['user','category'])->first();

You can then access the properties as follows:

// Retrieve user name
$article->user->user_name

// Retrieve category name
$article->category->category_name

Additionally, you can also retrieve articles by their category or user:

$categories = \App\Models\Category::with('articles')->get();
$users = \App\Models\User::with('articles')->get();

For further details, refer to the official Laravel documentation: http://laravel.com/docs/5.0/eloquent

The above is the detailed content of How to Join Three Tables in Laravel Using Eloquent Models?. For more information, please follow other related articles on the PHP Chinese website!