CODEWITHSUNDEEP
laraveleloquenteloquent-relationship
OutForCode
OutForCode

Reputation: 451

How to join two or more table using laravel eloquent method?

Little confused about eloquent-relationship joins, since I used to get the result by query builder so far. Referred with other related questions still, I am not clear. Please explain me with a better example.

Now I want to list the item details that customer related to.

join customer_items with items where customer.id = customer_items.user_id and items.id = customer_items.item_id.

Upvotes: 2

Views: 6341

Answers (3)

amba patel
amba patel

Reputation: 424

First define methods in models.

Customer.php // Customer model

class Customer extends Model
{
    protected $table = 'customers';
    protected $primaryKey = 'customer_id';

    public function customerItems(){

    //customer_id is a foreign key in customer_items table

    return $this->hasMany(Item::class, 'customer_id');

    // A customer will has many items thats why hasMany relation is used here
     }

    }

CustomerItem.php // CustomerItem

class CustomerItem extends Model
{
    protected $table = 'customer_items';
    protected $primaryKey = 'customer_item_id';

    public function itemDetail(){

     //customer_id is a foreign key in customer_items table

     return $this->hasOne(Customer::class, 'customer_item_id');

                //A Item will has single detail thats why hasOne relation used here
     }

    }

In CustomerController.php

use \Customer                  // define customer model path 

public function getCustomerItem(Request $request)
{
    // Eloquent query to get data
    $customer_item_detail_data = Customer::with('customerItems.itemDetail')->get();
    //it return all items of customers with item details 
    //$customer_item_detail_data = Customer::with('customerItems')->with('customerItems.itemDetail')->get(); you can also use in this way

}

Hope it helps. Thank you.

Upvotes: 2

Davide Casiraghi
Davide Casiraghi

Reputation: 18087

If I got well your question, you are looking for a query to get the item details.

$item_details = Items::
    join('customer_items', 'customer_items.item_id', '=', 'items.id')
    ->join('customer', 'customer.id' '=', 'customer_items.customer_id');

Or you can get the same result doing:

$item_details = DB::table('items')
    ->join('customer_items', 'customer_items.item_id', '=', 'items.id')
    ->join('customer', 'customer.id' '=', 'customer_items.customer_id');

Upvotes: 2

Bruno Francisco
Bruno Francisco

Reputation: 4220

First, you would need to define your models as such:

class Customer extends Model
{
    protected $table = 'customers';

    public function items(){
        return $this->hasMany(Item::class, 'customer_id');
    }

}

class CustomerItem extends Model
{
    protected $table = 'customer_items';

    public function customer(){
        return $this->belongsTo(Customer::class, 'customer_id');
    }

}

Then you would call the the relationship as such:

$customer = Customer::find(1); // This will get the first customer in the DB
$itemsOfCostumer = $customer->items // This will return all the items of the customer

// Now let suppose we have an ItemCustomer and we would like to know the owner
$customerItem = CustomerItem::find(1); // Get the first item of a customer in DB
$customer = $customerItem->customer; // Ther you have the customer

This is just a small example. Stackoverflow is not an educational website which I would highly advise you to visit Laravel Relationship Docs. Over there you can learn much more and they have a really good series at Laracast about relationships (if you are visual learner) https://laracasts.com/series/eloquent-relationships

Upvotes: 2

Related Questions