How to show attribute family having at least one attribute in eloquent relationship?

Question

I've three models:

1. product.php

class Product extends Model
{
    protected $table = 'products';

    // many to many relationship with product and attribute.
    public function attributes()
    {
        return $this->belongsToMany(Attribute::class);
    }

    
}

2. Attributefamily.php

    {
            use HasFactory;
        
            protected $table = 'attributefamilies';
        
            protected $fillable = [
                'name',
                'order',
            ];
        
        
            public function scopeAsc($query)
            {
                return $query->orderBy('order', 'asc');
            }
        
        
            public function attributes()
            {
                return $this->hasMany(Attribute::class, 'attributefamilies_id');
            }
     }

3. Attribute.php

class Attribute extends Model
    {
        protected $table = 'attributes';
    
        public function attribute_family()
        {
            return $this->belongsTo(Attributefamily::class, 'attributefamilies_id');
        }
    
        
        public function products()
        {
            return $this->belongsToMany(Product::class);
        }
    }

My Product Controller to show product look like this:

class ProductController extends Controller
{
    public function show($slug)
    {
        $product = Product::with(['product_category', 'brand', 'attributes'])
        ->where('slug', $slug)->firstOrFail();

        $attribute_families = Attributefamily::with('attributes')->get();



        return view('frontend.product', compact(
            'product',
            'attribute_families'
        ));
    }
}

In my product.blade.php

@if ($product->attributes->isNotEmpty())
    <div class="tab-pane fade show active':'' id="two" 
    role="tabpanel" aria-labelledby="two-tab">
        <div class="ipl-desc">
             <table>                               
                  @foreach ($attribute_families as $family)
                     <tr>
                        <td>{{ $family->name }}</td>
                        @foreach ($family->attributes as $attribute)
                            <td>{{ ($product->attributes->contains($attribute->id))?$attribute->name:'' 
                            }}</td>
                        @endforeach
                      </tr>
                     @endforeach
               </table>
        </div>
   </div>
@endif

My product page look like this:

At the end, what I want is to show only those attribute family having at least one attribute like this:

color red, green, blue.
size xl, m, sm.
//trim ram, processor, display, etc.

Alternatively I've though of doing this by changing query like this:

class ProductController extends Controller
    {
        public function show($slug)
        {
            $product = Product::with(['product_category', 'brand', 'attributes' => function ($query) {
                $query->with('attribute_family');
            }])
            ->where('slug', $slug)->firstOrFail();
    

            return view('frontend.product', compact(
                'product',
            ));
        }
    }

And at the end can't process more than that.

Answer 1

I'm not sure whether I followed standard answer or not but for now i solved it like this: ProductController.php

        // for displaying product.
        $product = Product::with(['product_category', 'brand'])
            ->where('slug', $slug)
            ->first();

        // product with attribute family and attribute
        $products_with_afa = DB::table('products')
            ->join('attribute_product', 'products.id', '=', 
            'attribute_product.product_id')
            ->join('attributes', 'attributes.id', '=', 'attribute_product.attribute_id')
            ->join('attributefamilies', 'attributefamilies.id', '=', 
            'attributes.attributefamilies_id')
            ->select(
                DB::raw('GROUP_CONCAT(attributes.name) as attr_names'),
                DB::raw('GROUP_CONCAT(attributes.color_code) as attr_color_codes'),
                'attributefamilies.name as family_name'
            )
            ->groupBy('attributefamilies.name')
            ->where('products.slug', $slug)
            ->get();

In my product.blade.php

@foreach ($products_with_afa as $product_with_afa)
<tr>
    <td>{{ $product_with_afa->family_name }}</td>
    
    @if ($product_with_afa->family_name == 'color')
        @foreach ( explode( ",", $product_with_afa->attr_color_codes ) as $attr_name)
            <td style="background-color:{!! $attr_name !!}"></td>
        @endforeach
    @else
        @foreach ( explode( ",", $product_with_afa->attr_names ) as $attr_name)
            <td>{{ $attr_name }}</td>
        @endforeach
    @endif
</tr>
@endforeach

My result look like this:

Those attribute families which a product does not use are not shown. Suggest if you have better idea.