How to join three table by laravel eloquent model

Question

I have three table

Articles table

 id
 title
 body
 categories_id
 user_id

Categories table

  id
  category_name

User table

 id
 user_name
 user_type

I want to show articles with their category name instead of category_id and user_name instead of user_id I try like these query It is work!

$articles =DB::table('articles')
                ->join('categories', 'articles.id', '=', 'categories.id')
                ->join('users', 'users.id', '=', 'articles.user_id')
                ->select('articles.id','articles.title','articles.body','users.username', 'category.name')
                ->get();

But I want to do by Eloquent way. Please, how could I do?

Thimble

How about creating a view that joins those tables, then you create Eloquent model that just reads from it? Code complexity reduced, you profit, no hard work involved. – Palindrome

All other related model and function are work together with Eloquent Model. So I need to use Eloquent Way . – Thimble

Are you mistaken here: ->join('categories', 'articles.id', '=', 'categories.id')? Instead of articles.id it should be articles.categories_id. Or am i wrong? – Odaniel

Answer 1

With Eloquent it's very easy to retrieve relational data. Check out the following example with your scenario in Laravel 5.

We have three models:

1. Article (belongs to user and category)

2. Category (has many articles)

3. User (has many articles)

---

1. Article.php

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

2. Category.php

    <?php
    namespace App\Models;
    
    use Eloquent;
    
    class Category extends Eloquent {
        protected $table = "categories";
    
        public function articles() {
            return $this->hasMany('App\Models\Article');
        }
    }

3. User.php

    <?php
    namespace App\Models;
    use Eloquent;
    
    class User extends Eloquent {
        protected $table = 'users';
    
        public function articles() {
            return $this->hasMany('App\Models\Article');
        }
    }

You need to understand your database relation and setup in models. The user has many articles. The category has many articles. Articles belong to user and category. Once you set up the relationships in Laravel, it becomes easy to retrieve the related information.

For example, if you want to retrieve an article by using the user and category, you would need to write:

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

and you can use this like so:

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

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

In another case, you might need to retrieve all the articles within a category or retrieve all of a specific user`s articles. You can write it like this:

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

You can learn more at http://laravel.com/docs/5.0/eloquent

Keil

Thanks for your reply But I use laravel 4 and want return data by json format .Like these return Response::json( array( 'total_count' => $count, 'incomplete_results' => false, 'items'=> $articles->toArray() )); How could I do for json format? – Thimble

no matter all code works in Laravel 4 just you need to remove namespace, and $articles->toArray() works same, please try – Keil

Thanks you brother it is work and You save my life :). Sorry for late reply Because I m not near computer in yesterday. – Thimble

I am not sure, but it seems that this way (using eloquent) generates 3 queries. While the query with custom join's will be single. So the answer by @NayZawOo below is more acceptable. – Odaniel

what if I don't want to access it like $article->category->category_name, but use some alias like $article->categoryName, how do I write eloquent query in that case? – Tranche

$users = \App\Models\Category::with('users')->get(); this would actually not work as there is no relation set between users and categories, right ? You have to go through articles first. I'm interested to know how to find all users that have one or more post in a specific category. – Better

This is working fine.I have one doubt that is how i fetched specific field name only.and can i perform DB::raw(count(),sum()) etc in belongsTo() – Gherlein

Imagine you wanna list 100 rows. Eloquent sends 300 queries to database!!! – Formative

hi @AnandPatel i need help i am using this query $semesterCourses = SemesterCourse::join('TimeTable' , 'timeTable.SemCourse_ID' , 'semesterCourses.ID') ->where('Day' , $request->Day) ->get(); it works fine but i want to use model so in semester course model i used this public function timeTable(){ return $this->belongsTo('App\Models\TimeTable' , 'SemCourse_ID'); } and in controller i used SemesterCourse::with('timeTable')->where('Day' , $request->Day)->get() but it does not gives record and with is empty – Stormi

Answer 2

Try:

$articles = DB::table('articles')
            ->select('articles.id as articles_id', ..... )
            ->join('categories', 'articles.categories_id', '=', 'categories.id')
            ->join('users', 'articles.user_id', '=', 'user.id')

            ->get();

Sausage

I want json object format return . Because I need to try $articles->count() and $articles->offset($offset); $articles->get(array('id','title','body')); But Your way can't apply those object functions. Thanks your support . – Thimble

return Response:json($articles); – Sausage

Although this isn't the eloquent way of doing it, it is the only way if you are sorting by fields in foreign tables. (which I wouldn't recommend, but is occasionally necessary). Because of that, I think this is still a very valid answer. (the question asked how to join tables, the eloquent way actually never joins them in the original query.) – Showpiece

this package cal help you github.com/fico7489/laravel-eloquent-join – Pubescent

"Although this isn't the eloquent way of doing it, it is the only way if you are sorting by fields in foreign tables. " Skeets, When you say the only way,,are you saying the laravel docs omit this important information ? – Varuna

This is not the eloquent style what the question asked – Careworn

@FarhanIbnWahid As far as I can tell, there is no Eloquent way to join. You can use relations, which performs additional queries, or you can use the DB query builder to perform joins. – Firecrest

Answer 3

$articles =DB::table('articles')
                ->join('categories','articles.id', '=', 'categories.id')
                ->join('user', 'articles.user_id', '=', 'user.id')
                ->select('articles.id','articles.title','articles.body','user.user_name', 'categories.category_name')
                ->get();
return view('myarticlesview',['articles'=>$articles]);

Protohuman

Above Query has Integrity constraint violation error that was solved in this answer – Protohuman