CODEWITHSUNDEEP
phpmysqllaravellaravel-5eloquent
AlShaikh
AlShaikh

Reputation: 43

How to get data from two related tables in laravel 5

I have 3 tables with foreign keys installed.

customers {customer_id, customer_name}

products {product_id, product_name}

customer_products {id, customer_id (foreignkey), product_id (foreignkey)}

My Controller code:

$CustomerProducts = ModelName::where('customer_id', 'somevalue')
->Join('customer_products', 'product_id', '=', 'customer_id')       
->get();

My Model code:

class ModelName extends Model { 
protected $table = 'hd_products';
public $primaryKey = 'id'; }

What is wrong in my code, since I'm getting wrong results. I want to show customer information and its related products.

Upvotes: 0

Views: 364

Answers (2)

Hikmat Mohammed
Hikmat Mohammed

Reputation: 48

If you want to show customer information and its related products, you gotta select the data from the tables.

In your code, in the controller, to get all data from all tables you add:

->select(['customers.*' ,'products.*' ,'customer_products.*'])->get();

and edit the join statement so the controller will be like: 

$CustomerProducts= DB::table('customer_products')
      ->join('customers','customers.customer_id','customer_products.customer_id')
      ->join('products','products.product_id','customer_products.product_id')
      ->select(['customers.*' ,'products.*' ,'customer_products.*'])
      ->get();

do not forget to add (if not added)

use DB;

At the beginning of your file (in the namespace area or the imports area), so it is like: 

namespace App\Http\Controllers;
use DB;
use App\ //"your_file";
use Illuminate\Http\Request;

Hope this is helpful :)

Upvotes: 0

Watercayman
Watercayman

Reputation: 8168

This is where Laravel makes life easy. By adding the relationships on the model, you can then simply call the relationship via an eager load. You don't need the join, you can just pull the relationship. So

On your Customer model, set up the product relationship (you look like you have the right database structure for a many-to-many):

 public function products(){
    return $this->belongsToMany("\App\Product");
 }

And then in your Controller, when you go to load your customers, you can grab the products at the same time:

$customer = Customer::with("products")->first();

I'm just taking the first customer as an example - you could get all of them and loop on customer as well as products if you wish.

And finally when you want to call the data like in a blade view, you can access it by chaining on the $customer model. :

{{ $customer->products->first()->name }}

If you want to loop through the products on the customer in a blade view:

@foreach($customer->products as $product){}

And, you still have the primary data for the $customer:

$customer->name // Etc.

HTH

Upvotes: 3

Related Questions