GROUP BY the relationship data in laravel

Question

I am fetching products along with its relationship. I have done this:

$data = $this->products->with('images')->with('colors')->where('slug', $slug)->first();

And in the Product model, I have written:

public function images(){
  return $this->hasMany('App\Models\ProductImages', 'product_id');
}

public function colors(){
  return $this->hasMany('App\Models\ProductSizes', 'color_id');
}

I am storing color_id in the product_sizes table so now when I do dd($data). It gives me 5 data inside the object where the size_id are different but the color_id are same. Is it possible to group the data coming in colors relationship?

I tried using array_unique in the blade but that did not gave me to use the following function:

public function colorInfo(){
    return $this->belongsTo('App\Models\Color', 'color_id');
}

I want to group the color_id coming in the colors relationship to display available colors of the product.

Code as per request: Product Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $fillable = ['name','slug','sku','category_id','brand_id','video','status', 'specification', 'description','warranty', 'vendor_id'];

public function getRules(){
    $rules = [
        'name' => 'bail|required|string|unique:products,name',
        'slug' => 'bail|required|string|unique:products,slug',
        'sku' => 'bail|required|string|unique:products,sku',
        'specification' => 'bail|required|string',
        'description' => 'bail|required|string',
        'category_id' => 'required|exists:product_categories,id',
        'brand_id' => 'nullable|exists:brands,id',
        'vendor_id' => 'nullable|exists:vendors,id',
        'video' => 'nullable|string',
        'warranty' => 'nullable|string',
        'status' => 'nullable|in:active,inactive',
    ];
    if($rules != 'add'){
        $rules['name'] = "required|string";
        $rules['slug'] = "required|string";
        $rules['sku'] = "required|string";
    }
    return $rules;
}
public function category(){
    return $this->belongsTo('App\Models\ProductCategory');
}

public function brand(){
    return $this->belongsTo('App\Models\Brand');
}

public function VendorName(){
    return $this->belongsTo('App\Models\Vendor', 'vendor_id');
}

public function images(){
  return $this->hasMany('App\Models\ProductImages', 'product_id');
}

public function sizes(){
  return $this->hasMany('App\Models\ProductSize', 'product_id');
}

public function colors(){
    return $this->hasMany('App\Models\ProductSize', 'product_id');
}

public function finalCategory(){
    return $this->belongsTo('App\Models\SecondaryCategory', 'category_id');
}

}

PRoduct Sizes Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class ProductSize extends Model
{
    protected $fillable = ['product_id','size_id', 'selling_price', 'purchase_price', 'discount','stock', 'color_id', 'quantity','total_price'];

public function getRules(){
    $rules = [
        'product_id' => 'required|exists:products,id',
        'size_id' => 'required|exists:sizes,id',
        'color_id' => 'required|exists:colors,id',
        'selling_price' => 'required|string',
        'purchase_price' => 'required|string',
        'quantity' => 'required|string',
        'total_price' => 'required|string',
        'discount' => 'nullable|string',
        'stock' => 'required|string',
    ];
    return $rules;
}

public function colorInfo(){
    return $this->belongsTo('App\Models\Color', 'color_id');
}
}

Aashish gaba · Accepted Answer · 2020-07-23T13:51:56.983

1

Get the product first.

$data = $this->products->with(['images', 'colors'])->where('slug', $slug)->first();

To get the distinct colors for that product.

$unique_product_colors = $data->colors->unique('color_id');

unique('color_id') method can be applied on a collection instance to get a new collection in which all the items will have unique color_id

edited Jul 23 '20 at 13:51

answered Jul 23 '20 at 12:58

Aashish gaba

1,726
1
5
14

Thanks Aashish. But it give this error Syntax error or access violation: 1055 'jojayo.product_sizes.id' isn't in GROUP BY (SQL: select * from `product_sizes` where `product_sizes`.`product_id` in (107) group by `color_id`) – Alisha Lamichhane Jul 23 '20 at 13:01
Could you share the model of product_size, product, color and their tables with properties? That would make it clear of how the structure is. Thanks – Aashish gaba Jul 23 '20 at 13:05
1

I've updated the solution accordingly. Hope it helps. Let me know if there's anything wrong with. – Aashish gaba Jul 23 '20 at 13:52

score 0 · Answer 2 · answered Jul 23 '20 at 12:56

0

Try This

 $data = $this->products->with('images')->with('colors')->where('slug', $slug)->groupBy('product_sizes.color_id')->first();

answered Jul 23 '20 at 12:56

xXx_HAWK

13
3

GROUP BY the relationship data in laravel

2 Answers2