Filament•10h ago

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óczi•9h 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•9h 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óczi•9h 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•8h 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óczi•8h 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•8h ago

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

Roland Barkóczi•8h 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•8h ago

Can you share the code of your table columns?

Roland Barkóczi•8h 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•8h 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óczi•8h ago

awcodes•8h ago

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

Roland Barkóczi•8h ago

awcodes•8h 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óczi•8h 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•8h 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óczi•8h ago

yeah, it's okay.

awcodes•8h ago

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

Roland Barkóczi•8h ago

LOL, I found a workaround to solve this issue:

Roland Barkóczi•8h 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•8h ago

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

Roland Barkóczi•8h 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•8h ago

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

Roland Barkóczi•8h ago

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

awcodes•8h ago

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

Roland Barkóczi•8h ago

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

awcodes•8h ago

Glad you got something working.

Roland Barkóczi•8h 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•8h ago

No worries. Just hope the back and forth helped.

Roland Barkóczi•7h ago

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

awcodes•7h 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.

Gaming

Programming

How to manage ManyToMany relation in TableBuilder and FormBuilder?