Eloquent best approach

To achieve the desired result, you can use Eloquent's relationships and eager loading to efficiently retrieve the attribute values grouped by attribute for a product. Here's a step-by-step solution:

1. Define the relationships in your Eloquent models.

class Product extends Model
{
    public function variants()
    {
        return $this->hasMany(ProductVariant::class);
    }
}

class ProductVariant extends Model
{
    public function attributeValues()
    {
        return $this->belongsToMany(AttributeValue::class);
    }
}

class Attribute extends Model
{
    public function values()
    {
        return $this->hasMany(AttributeValue::class);
    }
}

class AttributeValue extends Model
{
    public function attribute()
    {
        return $this->belongsTo(Attribute::class);
    }
}

1. Create a method in the Product model to retrieve the attribute values grouped by attribute.

class Product extends Model
{
    // ... existing relationships ...

    public function getAttributeValuesGroupedByAttribute()
    {
        return $this->variants()
            ->with('attributeValues.attribute')
            ->get()
            ->pluck('attributeValues')
            ->flatten() // Flatten the collection of collections into a single collection
            ->groupBy(function ($attributeValue) {
                return $attributeValue->attribute->name; // Group by attribute name
            });
    }
}

1. Use the method to get the attribute values for a specific product.

$product = Product::find($productId); // Replace $productId with the actual product ID
$groupedAttributeValues = $product->getAttributeValuesGroupedByAttribute();

// If you want to get only the colors
$colors = $groupedAttributeValues->get('Color'); // Replace 'Color' with the actual attribute name for colors

This approach will give you a collection where each key is an attribute name and the value is a collection of AttributeValue models that belong to that attribute. If you want to get all the colors that variants of the product have, you can simply access the collection by the attribute name (e.g., 'Color').

Keep in mind that this solution assumes that you have a 'name' column in your attributes table to identify the attribute by name. Adjust the code accordingly if your database schema is different.