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?
Advertisement
Answer
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:
Article (belongs to user and category)
Category (has many articles)
User (has many articles)
- Article.php
<?php namespace AppModels; use Eloquent; class Article extends Eloquent { protected $table = 'articles'; public function user() { return $this->belongsTo('AppModelsUser'); } public function category() { return $this->belongsTo('AppModelsCategory'); } }
- Category.php
<?php namespace AppModels; use Eloquent; class Category extends Eloquent { protected $table = "categories"; public function articles() { return $this->hasMany('AppModelsArticle'); } }
- User.php
<?php namespace AppModels; use Eloquent; class User extends Eloquent { protected $table = 'users'; public function articles() { return $this->hasMany('AppModelsArticle'); } }
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 = AppModelsArticle::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 = AppModelsCategory::with('articles')->get(); $users = AppModelsCategory::with('users')->get();
You can learn more at http://laravel.com/docs/5.0/eloquent