Laravel nested join using query builder?

Question

My scenario is I have 3 different tables

tblProducts -- List of Products --

     id (Primary Key)
     product_title
     deleted (Default value is 0 if not deleted, if deleted value is 1. Same below)

tblClass -- List of Classes --

     id (Primary Key)
     class_title
     deleted

tblClassPerProduct -- Assigned Classes per specific product--

     id (Primary Key)
     product_id (Key : tblProducts)
     class_id  (Key : tblClass)
     deleted

So what I'm trying to do is to get the nested json query of this relationship

Expected output using query builder

 [{
    "id" : 1,
    "product_title" : "Product1",
    "deleted " : 0,
    "classes_per_product" : 
      {
         "id": 1,
         "product_id" : 1,
         "class_id": 1,
         "class_title":"class 1",
         "deleted_class_per_product" : 0,
         "deleted_class" : 0
      },
      {
         "id": 2,
         "product_id" : 1,
         "class_id": 2,
         "class_title":"class 2",
         "deleted_class_per_product" : 0,
         "deleted_class" : 0
      },
      {
         "id": 3,
         "product_id" : 1,
         "class_id": 3,
         "class_title":"class 3",
         "deleted_class_per_product" : 0,
         "deleted_class" : 0
      },

 .....So on

I just want to get all classes assigned on each products

Currently I have this code

 $data = DB::table('tblProducts')
->select( 'tblProducts.id', 'tblProducts.product_title', 'tblProducts.deleted' )
->where('tblProducts.deleted',0)
->get();

 return $data;

Trying to do the nested json response.

One option is, doing it in eloquent model relationship. But my problem is there is existing query that I need to follow. So I simplify the things that I need in this question.

You can use ->join() just like in a regular query. Although you will not receive nested data like your expected result, since the raw query builder will only return flat row objects (basically raw SQL output). — Flame
– Flame, Commented Aug 27, 2020 at 9:29
the problem is i have multiple classes per product. if the query is flat row objects. there's a tendency that some of the classes possibly wont get since it's multiple? — Pablo
– Pablo, Commented Aug 27, 2020 at 9:31

macleash · Accepted Answer · 2020-08-27 11:20:51Z

I would advise you to use Eloquent relationships since its more efficient, if you use this DB query builder approach you will have serious performance issues when the data grows and users try to access that resource. Nevertheless I would show you how to use the DB query builder to get the data you want, then will show you the right way to do It with Eloquent relationships

$data = \DB::table('tblProducts')
    ->select( 'tblProducts.id', 'tblProducts.product_title', 'tblProducts.deleted' )
    ->where('tblProducts.deleted',0)
    ->get();
foreach($data as $product)
{
 $product->classes_per_product[] = \DB::table('tblClassPerProduct')->where("product_id", $product->id)->get();
}
return $data;

The right way to go about it with relationships is this

//in TblProduct.php 
protected $table = "tblProducts";
public function classes_per_product()
{
 return $this->hasMany('App\ClassProduct','product_id','id'); //where ClassProduct is the model that corresponds to the table tblClassPerProduct
}

//in your controller
return $data = TblProduct::with('classes_per_product')->get();

NikuNj Rathod · Accepted Answer · 2020-08-27 11:19:28Z

You can try given solution for you problem.

Products Model: Products.php

<?php
 
namespace App;
use Illuminate\Database\Eloquent\Model;
 
class Products extends Model
{
    public $table = 'tblProducts';

    public function classPerProductRel()
    {
        return $this->hasMany(ClassPerProduct::class,'product_id','id')->where('deleted',0);       
    }
}
?>

ClassPerProduct Model: ClassPerProduct.php

<?php
 
namespace App;
use Illuminate\Database\Eloquent\Model;
 
class ClassPerProduct extends Model
{
    public $table = 'tblClassPerProduct';
}
?>

You can get output

<?php 
$productsAll = Products::with('classPerProductRel')->orderBy('product_title')->get();

// Gel All product
#echo '<pre>';print_r($productsAll);exit;

// Get All product with iteration
foreach ($productsAll as $key => $value) {
    # code...
    echo $value->product_title."<br/>";

    // Get All  Assigned Classes per specific product--
    if(!empty($value->classPerProductRel)){
        foreach ($value->classPerProductRel as $innerKey => $innerValue) {
            # code...
            echo $innerValue->product_id."<br/>";   
            echo $innerValue->class_id."<br/>";
        }
    }
}
?>

Collectives™ on Stack Overflow

Laravel nested join using query builder?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related