How to join three table by laravel eloquent model
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 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
<?php
namespace App\Models;
use Eloquent;
class Category extends Eloquent {
protected $table = "categories";
public function articles() {
return $this->hasMany('App\Models\Article');
}
}
- 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
Laravel join three tables with relationships
As you have not defined relationships, we have to go with Query builder, Update your joins as below
$payments = DB::table('sales_payments')
->join('sales_invoices', 'sales_invoices.invoice_id', '=', 'sales_payments.invoice_id')
->join('customers', 'sales_invoices.customer_id', '=', 'customers.customer_id')
->join('payment_options', 'payment_options.paymentoption_id', '=', 'sales_payments.paymentoption_id')
->select('sales_payments.*', 'sales_invoices.*', 'customers.*', 'payment_options.*')
->get();
Then you can add where clause.
Laravel eloquent join three table
Change your query like this :
$product = product::Select("*")
//->with('product_history','product_tour')
//Use this with() for condition.
->with([
'ProductHistory' => function ($query){
$query->select('*');
},
'ProductTour' => function ($query) use ($ProviderName) {
$query->select('*')->where("provider_name", "=", $ProviderName);
},
])
->where('product.product_id', '1')
->get()
->toArray();
Here is the code for model file:
namespace App;
use DB;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{
public function producthistory()
{
return $this->hasMany('App\ProductHistory', 'product_id','history_id');
}
public function producttour()
{
return $this->hasMany('App\ProductTour', 'product_id','tour_id');
}
}
And model files for the other tables named as Product_history
and product_tour
namespace App;
use Illuminate\Database\Eloquent\Model;
class ProductHistory extends Model
{
public function products()
{
return $this->belongsTo('App\Product', 'history_id','product_id');
}
}
And
namespace App;
use Illuminate\Database\Eloquent\Model;
class ProductTour extends Model
{
public function products()
{
return $this->belongsTo('App\Product', 'tour_id','product_id');
}
}
How to join 3 tables using laravel eloquent
//post model
public function comments(){
return $this->hasMany('App\Comments', 'post_id', 'id');
}
//comments model
public function user(){
return $this->belongsTo('App\Profile', 'user_id');
}
//profile model
public function comments(){
return $this->hasMany('App\Comments');
}
and i call it in the controller using:
$posts = Posts::with('userProfile','comments', 'comments.user')->get();
Then it works:)
Thank you for some help and idea :)
How to make relationship between three table in laravel eloquent
You can use nested relation in with like below.So it will fetch question options also
$data=Section::with(['questions','questions.options'])
->where('formid',$formId)
->orderBy('sectionid')
->get()
->toJson();
Laravel Query Builder - Join three tables (A one to many B, B one to many C)
Leveraging Eloquent Relationships
Models & Relationships
class Country extends Model
{
public function states()
{
return $this->hasMany(State::class);
}
}
class State extends Model
{
public function country()
{
return $this->belongsTo(Country::class);
}
public function cities()
{
return $this->hasMany(City::class);
}
}
class City extends Model
{
public function state()
{
return $this->belongTo(State::class);
}
}
Query
$countries = Country::query()
->with('states.cities')
->get();
Laravel eloquent with multiple inner joins
Models\Entries.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Entries extends Model
{
public function Tasks(){
return $this->hasOne(Tasks::class);
}
public function Class(){
return $this->hasMany(Classes::class);
}
public function SubClasses(){
return $this->hasOne(SubClasses::class);
}
}
Models\Tasks.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Tasks extends Model
{
public function Entries(){
return $this->belongsTo(Entries::class, "id", "task_id");
}
}
Models\Classes.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Classes extends Model
{
public function Entries(){
return $this->belongsTo(Entries::class, "class_id", "id");
}
}
Models\Subclasses.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class SubClasses extends Model
{
public function Entries(){
return $this->belongsTo(Entries::class, "id", "subclass_id");
}
}
Query:
Entries::with([
"Tasks",
"Classes",
"SubClasses"
])
->where("opportunity_id", $opportunity->id)
->groupBy("task_id")
->get();
Related Topics
Get Last Insert Id After a Prepared Insert with Pdo
Is It Okay to Use Array[Key] in PHP
PHP Array_Filter with Arguments
PHP Artisan Make:Auth Command Is Not Defined
Form Input Field Names Containing Square Brackets Like Field[Index]
How to Render a Datetime Object in a Twig Template
Laravel 5 Change Public_Path()
Using PHP/Apache to Restrict Access to Static Files (Html, CSS, Img, etc)
Installing Specific Laravel Version with Composer Create-Project
Str_Replace for Multiple Items
Could Not Instantiate Mail Function. Why This Error Occurring
Prevent Innodb Auto Increment on Duplicate Key
How to Get Current Time in Milliseconds in PHP
Require(Vendor/Autoload.Php): Failed to Open Stream
Passing a Variable from One PHP Include File to Another: Global VS. Not