count the amount of variations a product has on a filament table

i have a similar structure:

product -> product_variant (id) <- (product_variant_id) attribute_variants (attribute_id) -> attributes

the id of a product is represented within the product_id column of product_variants, and product_variant_id is represented within the the table "attribute_variants" to ultimately represent a product.
an attribute_id within attribute_variants is representing an "attribute" within the attributes table.

hope that makes sense..................

by doing this in tinker i was able to get this result:

$productVariants = ProductVariant::with('product')
    ->select('product_id', DB::raw('count(*) as variant_count'))
    ->groupBy('product_id')
    ->get();

foreach ($productVariants as $variant) {
    echo "Product Name: {$variant->product->name}, Variations: {$variant->variant_count}\n";
}

Product Name: Black metal hoodie, Variations: 2
Product Name: T-shirt, Variations: 2

im trying to get the same result rendered on a table with filament but i get duplications where, its giving me 4 results instead of 2, because i only have 2 products with their own variants.

ultimately i would like to display just the 2 products with whatever amount of variations the product has.

i've tried replicating the same query i made in tinker but the way im doing it wont display the amount of variants and it will display duplicate records.

class ProductVariantResource extends Resource
{
    public static function getTableQuery(): Builder
    {
        return ProductVariant::query()
            ->select('product_id', DB::raw('count(*) as variant_count'))
            ->with('product')
            ->groupBy('product_id');
    }

    public static function table(Table $table): Table
    {
        return $table
            ->columns([
                TextColumn::make('product.name')
                    ->label('Producto')
                    ->sortable()
                    ->searchable(),
                TextColumn::make('variant_count')
                    ->label('Variaciones')
                    ->sortable(),
    }
}

how can i count the records without duplication and by showing the products name?

the set relationships between a Product and a ProductVariant:

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

class ProductVariant extends Model
{

    public function product()
    {
        return $this->belongsTo(Product::class);
    }
}

LaryAI

1 year ago

Level 58

To achieve the desired result of displaying each product with the count of its variants without duplication in a Filament table, you need to ensure that your query and table configuration are correctly set up. The issue you're facing with duplication might be due to how the relationships and query are being handled. Here's a solution to address this:

Modify the Query: Ensure that the query correctly groups by product_id and counts the variants. You should also ensure that the with method is used correctly to eager load the related product.
Adjust the Table Configuration: Ensure that the table columns are correctly set up to display the product name and the count of variants.

Here's how you can adjust your ProductVariantResource:

use Filament\Resources\Resource;
use Filament\Tables;
use Filament\Tables\Columns\TextColumn;
use Illuminate\Database\Eloquent\Builder;
use App\Models\ProductVariant;

class ProductVariantResource extends Resource
{
    public static function getTableQuery(): Builder
    {
        return ProductVariant::query()
            ->select('product_id', DB::raw('count(*) as variant_count'))
            ->with('product') // Ensure the product relationship is eager loaded
            ->groupBy('product_id');
    }

    public static function table(Tables\Table $table): Tables\Table
    {
        return $table
            ->columns([
                TextColumn::make('product.name')
                    ->label('Product Name')
                    ->sortable()
                    ->searchable(),
                TextColumn::make('variant_count')
                    ->label('Variations')
                    ->sortable(),
            ]);
    }
}

Key Points:

Eager Loading: The with('product') ensures that the product data is loaded alongside the variants, which is necessary for accessing the product name in the table.
Grouping and Counting: The query groups by product_id and uses DB::raw('count(*) as variant_count') to count the number of variants for each product.
Table Columns: The TextColumn::make('product.name') and TextColumn::make('variant_count') are used to display the product name and the count of variants, respectively.

By ensuring that the query is correctly set up to group and count the variants, and that the table is configured to display the necessary data, you should be able to achieve the desired result without duplication.

RemiM

1 year ago

Level 16

You could probably modify the ProductResource instead of ProductVariantResource and use withCount() on your Eloquent Query Builder.

class ProductResource extends Resource
{
    public static function getTableQuery(): Builder
    {
        return Product::query()->withCount('variants');
    }

    public static function table(Table $table): Table
    {
        return $table
            ->columns([
                TextColumn::make('name')
                    ->label('Producto')
                    ->sortable()
                    ->searchable(),

                TextColumn::make('variants_count')
                    ->label('Variaciones')
                    ->sortable(),
            ]);
    }
}

Please or to participate in this conversation.