Laravel eloquent join three table

Question

I am new to Laravel. Now i need some help on join table eloquent.

Here is my table structure,

Product Table

product_id    product_code    product_name
     1            1434            ABC

Product History Table

history_id    product_id    cost    invoice_number
    1              1        100         ABC-01

Product Tour Table

tour_id       product_id    provider_name
    1              1            TEST

Now i join these 3 table,

$product =  product::join('product_history as ph', 'product.product_id', '=', 'ph.product_id', 'inner')
            ->join('product_tour as pt', 'product.product_id', '=', 'pt.product_id', 'inner')
            ->where('product.product_id', '1')
            ->get()
            ->toArray();

It is working properly. But i think Laravel also provide Eloquent Relationship. Like, hasOne, hasMany, belongsTo, belongsToMany etc....

Any idea which method is use for my structure ?

Thanks in advance.

Answer 1

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

}