Topics

Filament•2mo ago

Roland Barkóczi

How to manage ManyToMany relation in TableBuilder and FormBuilder?

I have the following models Product - id - name Pricelist - id - name ProductPrice - id - product_id - pricelist_id - price I want to create a dynamic table on product listing page, where all the Pricelist are listed as columns (e.g. Retail, Reseller, Discount) and respectively the belonging product prices. I defined these functions in Product model:

    public function prices(): HasMany
    {
        return $this->hasMany(ProductPrice::class, 'product_id');
    }

    public function price($pricelist_id)
    {
        return $this->prices()->where('pricelist_id', $pricelist_id)->first();
    }

    public function prices(): HasMany
    {
        return $this->hasMany(ProductPrice::class, 'product_id');
    }

    public function price($pricelist_id)
    {
        return $this->prices()->where('pricelist_id', $pricelist_id)->first();
    }

Furthermore I tried this code in the ProductRelationsManager table columns function:

...array_map(
  fn(Pricelist $pricelist) => TextColumn::make('price_' . $pricelist->id)
->label($pricelist->name), $this->getOwnerRecord()->pricelists()->get()->all())

...array_map(
  fn(Pricelist $pricelist) => TextColumn::make('price_' . $pricelist->id)
->label($pricelist->name), $this->getOwnerRecord()->pricelists()->get()->all())

So it creates all the pricelist columns, but I have no clue what should I add to the TextColumn::make() parameter in order to reach the given price belonging to the actual pricelist. Also have no clues how to define the form, what should I add to the TextInput::make() as parameter.

31 Replies

Roland BarkócziOP•2mo ago

I add the following function to the Product model

 public function __get($key)
    {
        //if $key matches price_pricelist, return the price
        if (preg_match('/^price_(\d+)$/', $key, $matches)) {
            return $this->price($matches[1])->price;
        }

        return parent::__get($key);
  }

 public function __get($key)
    {
        //if $key matches price_pricelist, return the price
        if (preg_match('/^price_(\d+)$/', $key, $matches)) {
            return $this->price($matches[1])->price;
        }

        return parent::__get($key);
  }

Tinker sais:

> $product->price_2
= "500.0000"

> $product->price_2
= "500.0000"

But if I add this to the TextColumn::make('price_2"), it does not show. If I add debug($key) to the magic __get() function, it even not print the price_2, all the other getter (eg: name, sku, category_id) are listed in the debugbar

awcodes•2mo ago

I’m not following exactly what you are trying to do, but this sounds more like a polymorphic many to many to me. Not a many to many. Dynamic relationship doesn’t make sense to me though.

Roland BarkócziOP•2mo ago

Hello Awcodes, I think this is a pure many to many relation: Product -> ProductPrice (Pivot) <- PriceList I modified the Product model, and added:

    public function pricelists(): BelongsToMany
    {
        return $this->belongsToMany(Pricelist::class, 'vendor_product_prices', 'product_id', 'pricelist_id')
            ->withPivot('price');
    }

    public function pricelists(): BelongsToMany
    {
        return $this->belongsToMany(Pricelist::class, 'vendor_product_prices', 'product_id', 'pricelist_id')
            ->withPivot('price');
    }

also the Pricelist model:

   public function products(): BelongsToMany
    {
        return $this->belongsToMany(Product::class, 'vendor_product_prices', 'pricelist_id', 'product_id');
    }

   public function products(): BelongsToMany
    {
        return $this->belongsToMany(Product::class, 'vendor_product_prices', 'pricelist_id', 'product_id');
    }

Question still open: how can I implement The following table:

name        | Retail price | Discount price | ... |
---------------------------------------------------
Product 1   | 1000.000.    |800.0000.       | .... 
---------------------------------------------------
Product 2   | 1200.000.    |1000.0000.       | ....

name        | Retail price | Discount price | ... |
---------------------------------------------------
Product 1   | 1000.000.    |800.0000.       | .... 
---------------------------------------------------
Product 2   | 1200.000.    |1000.0000.       | ....

awcodes•2mo ago

Table columns support dot syntax and will eager load the relationships as long as the relationship is setup correctly on the model. So TextColumn::make(‘products.price_2’) should work as expected for the relationship. I’m just not sure if the ‘2’ is trying to by dynamic or not in your use case.

Roland BarkócziOP•2mo ago

It should be dynamic. I tried, pricelist_2.price but does not work. I want to display somehow the following result in data table:

$product->pricelists()->where('pricelist_id', 2)->first()->pivot->price

$product->pricelists()->where('pricelist_id', 2)->first()->pivot->price

awcodes•2mo ago

Isn’t polymorphism the use case for dynamic relationships though?

Roland BarkócziOP•2mo ago

No, because the pivot table connects only these two tables. I can't see any polymorphic behaviour what is described here: https://laravel.com/docs/11.x/eloquent-relationships#many-to-many-polymorphic-relations

Laravel - The PHP Framework For Web Artisans

Laravel is a PHP web application framework with expressive, elegant syntax. We’ve already laid the foundation — freeing you to create without sweating the small things.

awcodes•2mo ago

Can you share the code of your table columns?

Roland BarkócziOP•2mo ago

Sure:

->columns([
        TextColumn::make('name')
                    ->label(__('Name'))
                    ->description(fn(Product $record): string => __('Short name') . ": " . $record->short_name . " | " . __('SKU') . ": " . ($record->sku ?? '-')),
                TextColumn::make('category.name')
                    ->label(__('Category')),
                ToggleColumn::make('is_active')
                    ->label(__('Active'))->toggleable(),
                ...array_map(
                    fn(Pricelist $pricelist) =>
                    TextColumn::make('pricelists.' . $pricelist->id . ".price")
                        ->label($pricelist->name),
                    $this->getOwnerRecord()->pricelists()->get()->all()
                )
            ])

->columns([
        TextColumn::make('name')
                    ->label(__('Name'))
                    ->description(fn(Product $record): string => __('Short name') . ": " . $record->short_name . " | " . __('SKU') . ": " . ($record->sku ?? '-')),
                TextColumn::make('category.name')
                    ->label(__('Category')),
                ToggleColumn::make('is_active')
                    ->label(__('Active'))->toggleable(),
                ...array_map(
                    fn(Pricelist $pricelist) =>
                    TextColumn::make('pricelists.' . $pricelist->id . ".price")
                        ->label($pricelist->name),
                    $this->getOwnerRecord()->pricelists()->get()->all()
                )
            ])

awcodes•2mo ago

Ok. It seems like you’re looking for columns on a table that may or may not exist. The db doesn’t have the concept of dynamic column / field names unless you explicitly define them. Ie, pricelists.price_2 may not exist for any given relationship. It’s just not how relationships work. The table column either exists or it doesn’t. I could be entirely missing the use case though. Sorry.

Roland BarkócziOP•2mo ago

No description

awcodes•2mo ago

Sorry, just sounds like something is off in the relationship to me.

Roland BarkócziOP•2mo ago

No description

awcodes•2mo ago

Ok, but none of these show any actual column being defined for product_2 for instance, so laravel can’t infer the relationship. This is why dynamic relationships don’t work. Maybe you need an explicit join vs a relationship.

Roland BarkócziOP•2mo ago

yeah, thats the problem. I can query the price if I iterate through the pricelists. My first idea was using the formatStateUsing() but if in case I add a non-existing or empty attribute to the TextColumn::make('xxxx') it shows nothing, and even the formatStateUsing is not called

awcodes•2mo ago

Maybe hasManyThrough is what you are looking for. Honestly, that’s a relationship type I have almost no experience with because it make my brain explode, but possible.

Roland BarkócziOP•2mo ago

yeah, it's okay.

awcodes•2mo ago

But yea, dynamic relationships just don’t make sense at the db level to me. But I could be completely wrong.

Roland BarkócziOP•2mo ago

LOL, I found a workaround to solve this issue:

No description

Roland BarkócziOP•2mo ago

The default() just inject the $record to the callback function. Of course the 'price_1' still not exists therefore it cannot display anything, but in the default() function I can query the necessary data

awcodes•2mo ago

Hey, if it works. 😆 I think you might have an n+1 issue though.

Roland BarkócziOP•2mo ago

Anyway, awcodes, thank you so much to try to help me. You're great! I gonna debug it, but I gonna figure out some caching these data in a Redis server

awcodes•2mo ago

But as long as your table isn’t ahowing too many records at one time the n+1 might not matter.

Roland BarkócziOP•2mo ago

yeah, use case is expecting here ~20 product/service per vendor

awcodes•2mo ago

Should be ok then. Just make it work first, then make it better.

Roland BarkócziOP•2mo ago

the form won't be a problem, I create some fancy component for it

awcodes•2mo ago

Glad you got something working.

Roland BarkócziOP•2mo ago

actually I use this data model for ages in my legacy e-commerce projects (even not laravel projects) thanks again for your support!

awcodes•2mo ago

No worries. Just hope the back and forth helped.

Roland BarkócziOP•2mo ago

actually the ->default() will show any data in the cell, even if the referenced attribute does not exists

awcodes•2mo ago

Default will show what you tell it to. I think there also a getStateUsing() or something like that which could be used instead of default.

Filament is a collection of beautiful full-stack components for Laravel.You can get help using it on our Discord.

18KMembers

View on Discord

Want results from more Discord servers?

Add your server