Filament•3mo ago

RelationManager belongsToMany withPivot(['id']) returns Column 'id' in order clause is ambiguous

When attaching an Product item to my Warehouse model which is a manyToMany relationship. I'm getting an SQL error (see below) the order by should contain the pivot table name .... i need the "id" because my attachments can be duplicated. I also already have configured: $table->allowDuplicates() this is my migration:

Schema::create('product_warehouse', function (Blueprint $table) {

            $table->id();
            $table->foreignId('product_id');
            $table->foreignId('warehouse_id');
            $table->string('quantity');
            $table->text('location_info');
            $table->timestamps();
        });

Schema::create('product_warehouse', function (Blueprint $table) {

            $table->id();
            $table->foreignId('product_id');
            $table->foreignId('warehouse_id');
            $table->string('quantity');
            $table->text('location_info');
            $table->timestamps();
        });

Product Model:

public function warehouses()
    {
        return $this->belongsToMany(Warehouse::class)->withPivot(['id', 'quantity', 'location_info']);
    }

public function warehouses()
    {
        return $this->belongsToMany(Warehouse::class)->withPivot(['id', 'quantity', 'location_info']);
    }

Warehouse Model:

public function products(): BelongsToMany
    {
        return $this->belongsToMany(Product::class)->withPivot(['id', 'quantity', 'location_info']);
    }

public function products(): BelongsToMany
    {
        return $this->belongsToMany(Product::class)->withPivot(['id', 'quantity', 'location_info']);
    }

SELECT
  `product_warehouse`.`warehouse_id` AS `pivot_warehouse_id`,
  `product_warehouse`.`product_id` AS `pivot_product_id`,
  `product_warehouse`.`quantity` AS `pivot_quantity`,
  `product_warehouse`.`location_info` AS `pivot_location_info`,
  `product_warehouse`.*,
  `products`.*
FROM
  `products`
  INNER JOIN `product_warehouse` ON `products`.`id` = `product_warehouse`.`product_id`
WHERE
  `product_warehouse`.`warehouse_id` = 1
ORDER BY
  `id` DESC
limit
  50 OFFSET 0

SELECT
  `product_warehouse`.`warehouse_id` AS `pivot_warehouse_id`,
  `product_warehouse`.`product_id` AS `pivot_product_id`,
  `product_warehouse`.`quantity` AS `pivot_quantity`,
  `product_warehouse`.`location_info` AS `pivot_location_info`,
  `product_warehouse`.*,
  `products`.*
FROM
  `products`
  INNER JOIN `product_warehouse` ON `products`.`id` = `product_warehouse`.`product_id`
WHERE
  `product_warehouse`.`warehouse_id` = 1
ORDER BY
  `id` DESC
limit
  50 OFFSET 0

51 Replies

LeandroFerreira•3mo ago

you don't need to add 'id' in the withPivot..

shenntek•3mo ago

@Leandro Ferreira documentations says you should.... https://filamentphp.com/docs/3.x/panels/resources/relation-managers#handling-duplicates

Managing relationships - Panel Builder - Filament

shenntek•3mo ago

or shouldnt i create an "id" column at the pivot table? and does laravel this automagically?

LeandroFerreira•3mo ago

$table->id(); + allowDuplicates() try this without 'id' in the withPivot..

shenntek•3mo ago

okay, if i remove the "id" column in my "product_warehouse" table. the page loads fine.. but when i click the edit action i get the following error:

Column not found: 1054 Unknown column 'product_warehouse.id' in 'field list'
SELECT
  `product_warehouse`.*,
  `products`.*,
  `product_warehouse`.`warehouse_id` AS `pivot_warehouse_id`,
  `product_warehouse`.`product_id` AS `pivot_product_id`,
  `product_warehouse`.`id` AS `pivot_product_warehouse.id`,
  `product_warehouse`.`quantity` AS `pivot_quantity`,
  `product_warehouse`.`location_info` AS `pivot_location_info`
FROM
  `products`
  INNER JOIN `product_warehouse` ON `products`.`id` = `product_warehouse`.`product_id`
WHERE
  `product_warehouse`.`warehouse_id` = 1
  AND `product_warehouse`.`id` = 1
limit
  1

Column not found: 1054 Unknown column 'product_warehouse.id' in 'field list'
SELECT
  `product_warehouse`.*,
  `products`.*,
  `product_warehouse`.`warehouse_id` AS `pivot_warehouse_id`,
  `product_warehouse`.`product_id` AS `pivot_product_id`,
  `product_warehouse`.`id` AS `pivot_product_warehouse.id`,
  `product_warehouse`.`quantity` AS `pivot_quantity`,
  `product_warehouse`.`location_info` AS `pivot_location_info`
FROM
  `products`
  INNER JOIN `product_warehouse` ON `products`.`id` = `product_warehouse`.`product_id`
WHERE
  `product_warehouse`.`warehouse_id` = 1
  AND `product_warehouse`.`id` = 1
limit
  1

@Leandro Ferreira do you mean:

public function table(Table $table): Table
    {
        return $table
        ->id()
        ->allowDuplicates()

public function table(Table $table): Table
    {
        return $table
        ->id()
        ->allowDuplicates()

or in the migration the $table->id() return $table->id() --> Method Filament\Tables\Table::id does not exist.

LeandroFerreira•3mo ago

migration, as you did...

shenntek•3mo ago

okay so table "product_warehouse" now has id with auto-increment , Warehouse.php model:

public function products(): BelongsToMany
    {
        return $this->belongsToMany(Product::class)->withPivot(['quantity', 'location_info']);
    }

public function products(): BelongsToMany
    {
        return $this->belongsToMany(Product::class)->withPivot(['quantity', 'location_info']);
    }

Product.php model:

 public function warehouses()
    {
        return $this->belongsToMany(Warehouse::class)->withPivot(['quantity', 'location_info']);
    }

 public function warehouses()
    {
        return $this->belongsToMany(Warehouse::class)->withPivot(['quantity', 'location_info']);
    }

still :

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in order clause is ambiguous
SELECT
  *
FROM
  `products`
  INNER JOIN `product_warehouse` ON `products`.`id` = `product_warehouse`.`product_id`
WHERE
  `product_warehouse`.`warehouse_id` = 1
ORDER BY
  `id` DESC
limit
  50 OFFSET 0

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in order clause is ambiguous
SELECT
  *
FROM
  `products`
  INNER JOIN `product_warehouse` ON `products`.`id` = `product_warehouse`.`product_id`
WHERE
  `product_warehouse`.`warehouse_id` = 1
ORDER BY
  `id` DESC
limit
  50 OFFSET 0

wait.. this "Column not found: 1054 Unknown column 'product_warehouse.id' in 'field list'". was something not correct i used withPivot(["product_warehouse.id", 'quantity', 'location_info']); obviously that doesnt work if i remove the "id" column from the pivot table "product_warehouse" and use ->withPivot(['id', 'quantity', 'location_info']); the RelationManager view page works.. but then in the edit action i gives me an error:

 EditAction::make()
                    ->form(fn (EditAction $action): array => [
                        $action->getRecordSelect(),
                        TextInput::make('quantity')->required(),
                        TextInput::make('location_info')->required(),
                    ]),

 EditAction::make()
                    ->form(fn (EditAction $action): array => [
                        $action->getRecordSelect(),
                        TextInput::make('quantity')->required(),
                        TextInput::make('location_info')->required(),
                    ]),

SQL error when clicking: "edit" on that record row...

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'product_warehouse.id' in 'field list'
SELECT
  `product_warehouse`.*,
  `products`.*,
  `product_warehouse`.`warehouse_id` AS `pivot_warehouse_id`,
  `product_warehouse`.`product_id` AS `pivot_product_id`,
  `product_warehouse`.`id` AS `pivot_id`,
  `product_warehouse`.`quantity` AS `pivot_quantity`,
  `product_warehouse`.`location_info` AS `pivot_location_info`
FROM
  `products`
  INNER JOIN `product_warehouse` ON `products`.`id` = `product_warehouse`.`product_id`
WHERE
  `product_warehouse`.`warehouse_id` = 1
  AND `product_warehouse`.`id` = 1
limit
  1

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'product_warehouse.id' in 'field list'
SELECT
  `product_warehouse`.*,
  `products`.*,
  `product_warehouse`.`warehouse_id` AS `pivot_warehouse_id`,
  `product_warehouse`.`product_id` AS `pivot_product_id`,
  `product_warehouse`.`id` AS `pivot_id`,
  `product_warehouse`.`quantity` AS `pivot_quantity`,
  `product_warehouse`.`location_info` AS `pivot_location_info`
FROM
  `products`
  INNER JOIN `product_warehouse` ON `products`.`id` = `product_warehouse`.`product_id`
WHERE
  `product_warehouse`.`warehouse_id` = 1
  AND `product_warehouse`.`id` = 1
limit
  1

so there it does expect that "id" column in the pivot table i think if I change the "id" column name in the pivot table to perhaps: 'product_warehouse_id' as auto-increment key... and use that in the with pivot no then still in editAction the same sql error because it gets the ID of the product ID table

LeandroFerreira•3mo ago

honestly, I think this would work with id and allowDuplicates.. If you want to create a mini repo on Github to reproduce this error, I can take a look after 👍

shenntek•3mo ago

so back to square one... and i think only the order by should add the table name of the products or the warehouse table...

SELECT
  `product_warehouse`.`warehouse_id` AS `pivot_warehouse_id`,
  `product_warehouse`.`product_id` AS `pivot_product_id`,
  `product_warehouse`.`quantity` AS `pivot_quantity`,
  `product_warehouse`.`location_info` AS `pivot_location_info`,
  `product_warehouse`.*,
  `products`.*
FROM
  `products`
  INNER JOIN `product_warehouse` ON `products`.`id` = `product_warehouse`.`product_id`
WHERE
  `product_warehouse`.`warehouse_id` = 1
ORDER BY
  `id` DESC
limit
  50 OFFSET 0

SELECT
  `product_warehouse`.`warehouse_id` AS `pivot_warehouse_id`,
  `product_warehouse`.`product_id` AS `pivot_product_id`,
  `product_warehouse`.`quantity` AS `pivot_quantity`,
  `product_warehouse`.`location_info` AS `pivot_location_info`,
  `product_warehouse`.*,
  `products`.*
FROM
  `products`
  INNER JOIN `product_warehouse` ON `products`.`id` = `product_warehouse`.`product_id`
WHERE
  `product_warehouse`.`warehouse_id` = 1
ORDER BY
  `id` DESC
limit
  50 OFFSET 0

so the ORDER BY would look like this:

ORDER BY `products`.`id`

ORDER BY `products`.`id`

ORDER BY `product_warehouse`.`id`

ORDER BY `product_warehouse`.`id`

awcodes•3mo ago

Shouldn’t these be HasMany instead of BelongsToMany? I could be wrong. Product has many warehouses and warehouse has many products. Neither actually ‘owns’ the other.

shenntek•3mo ago

that is true @awcodes i'm going to change it see if it works. thanks for the "detailed eye ;-)" btw in "CanSortRecords.php" this function:

protected function applyDefaultSortingToTableQuery(Builder $query): Builder
    {
        $sortColumnName = $this->getTable()->getDefaultSortColumn();

protected function applyDefaultSortingToTableQuery(Builder $query): Builder
    {
        $sortColumnName = $this->getTable()->getDefaultSortColumn();

returns just the "id" not with table name.. although $this->getTable().. and in CanBeSortable.php" this function just strips of everything... except behind last dot

**
     * @return array{0: string}
     */
    public function getDefaultSortColumns(): array
    {
        return [str($this->getName())->afterLast('.')];
    }

**
     * @return array{0: string}
     */
    public function getDefaultSortColumns(): array
    {
        return [str($this->getName())->afterLast('.')];
    }

HasMany.. is not with an pivot/intermediate table right??? okay i fixed it by adding: ->defaultSort('products.id', 'desc')

public function table(Table $table): Table
    {
        return $table
            ->defaultSort('products.id', 'desc')
            ->allowDuplicates()
            ->columns([
                TextColumn::make('id')
                    ->label(strval(__('id')))
                    ->searchable(),
                TextColumn::make('name')
                    ->label(strval(__('name')))
                    ->searchable(),
                TextColumn::make('sku')
                    ->searchable()
                    ->label(strval(__('sku'))),
                TextColumn::make('quantity'),
                TextColumn::make('location_info'),
            ])

public function table(Table $table): Table
    {
        return $table
            ->defaultSort('products.id', 'desc')
            ->allowDuplicates()
            ->columns([
                TextColumn::make('id')
                    ->label(strval(__('id')))
                    ->searchable(),
                TextColumn::make('name')
                    ->label(strval(__('name')))
                    ->searchable(),
                TextColumn::make('sku')
                    ->searchable()
                    ->label(strval(__('sku'))),
                TextColumn::make('quantity'),
                TextColumn::make('location_info'),
            ])

but the getDefaultSortColumns() is not correct it does not take in account when multiple tables are joined that it should use one table name in front of the sorting there goes a lot of things wrong with pivot when you have the same column names in multiple tables. for example in the intermediate/pivot table i have column "quantity" but that column also exists in the "products" table... when i now edit the record it takes the wrong "quantity"

awcodes•3mo ago

Many to many most definitely requires a pivot table.

shenntek•3mo ago

EditAction::make() ->form(fn (EditAction $action): array => [ TextInput::make('quantity')->required(), TextInput::make('location_info')->required(), ]), quantity in the editAction form is now the value of products.quantity (table) and not product_warehouse.quantity (table)

awcodes•3mo ago

Yea, if you need the info from a relationship then the form needs to know it’s a relationship. By default all properties on the record are assumed to be on the record itself and it’s tied to the state of the form. Anything in a relationship needs to be define with a form field or layout component that supports relationships.

shenntek•3mo ago

@awcodes that's why ->withPivot([]) on models... but if the names are the same in all tables you have a problem

awcodes•3mo ago

Well, yea, but that’s not necessarily a filament issue. That’s an issue of how you’re doing the joins.

shenntek•3mo ago

yes that i can agee on supplement columns like quantity, but not on the primary keys id

awcodes•3mo ago

Right but the relationship should handle the keys. The pivot attributes should never need the primary keys

shenntek•3mo ago

return $table ->defaultSort('products.id', 'desc') ->allowDuplicates() ->columns([ TextColumn::make('id') <-- actually is the products.id TextColumn::make('products.name') <-- does not work TextColumn::make('product_warehouse.id'), <-- does not work TextColumn::make('quantity'), <-- is products.quantity TextColumn::make('product_warehouse.quantity'), <-- does not work ]) that is what i think i would use: products.name or product_warehouse.quantity but that doesnt work

awcodes•3mo ago

Sorting on an id is always going to create problems.

shenntek•3mo ago

well the docs say it should work: https://filamentphp.com/docs/3.x/panels/resources/relation-managers#handling-duplicates

Managing relationships - Panel Builder - Filament

awcodes•3mo ago

You’re going to have to redefine any pivot relationship data that uses the same keys.

shenntek•3mo ago

yes i will do that for quantity etc... but for the id it should be handled by laravel/filament correctly

awcodes•3mo ago

It’s no different. Filament / Laravel shouldn’t do any of that automatically.

shenntek•3mo ago

because i already tried changing the id column of the intermediate table (product_warehouse) from id to product_warehouse_id and use that column as the withPivot(['product_warehouse_id' ... ])

awcodes•3mo ago

You can join in primary keys, it defeats the entire point of a primary key. But I’m also not fully understanding why you need the id instead of the relationship handling it. Like, what is the practical use case of sorting on the id?

shenntek•3mo ago

okay. the purpose is because one and the same product A can have laying around 4 pieces in warehouse A and 6 in warehouse B. so you need to assign the same. product A on warehouse A and on warehouse B ->defaultSort('products.id', 'desc') i need to set because the $query generates otherwise ORDER BY 'id' instead of ORDER BY 'products.id' it does not matter what i set in the default sort. it could also be ->defaultSort('warehouses.id', 'desc') or ->defaultSort('product_warehouse.id', 'desc') as long as i provide the prefix because filament just does not add it correctly in this setup i would expect that filament adds this product. prefix because:

class ProductRelationManager extends RelationManager
{

    protected static string $relationship = 'products';

class ProductRelationManager extends RelationManager
{

    protected static string $relationship = 'products';

so the productRelationManager knows that the relationship is products, and that model has an
relation to warehouses()... public function warehouses(): BelongsToMany { return $this->belongsToMany(Warehouse::class)->withPivot(['id', 'quantity', 'location_info']); } so when filament builds the query it should take in account that when it joins the intermediate table ( INNER JOIN product_warehouse ON products.id = product_warehouse.product_id) that sorting also should have the prefix of the base table (in my case 'products' it does set the "products.id" correctly to the ON so... why not on the ORDER BY I just think that in filament/tables/src/Table/Concerns/CanSortRecords.php:71 function protected function applySortingToTableQuery(Builder $query): Builder returns not the correct / full order by column

awcodes•3mo ago

Ok but your query is joining data and the warehouse id and product id could both be 2. So when that’s joined one of them has to overridden since they both can’t hav the same primary key. And that’s why you can’t sort on id with joins. The owning record will always trump it.

shenntek•3mo ago

that's why i added the id on the pivot table itself...

awcodes•3mo ago

I think you’re going to have to pick an order her and possible do a tuple sort. ->orderBy([warehouse.id, product.id]) Something like that. But depending on the model active at the time that can all change.

shenntek•3mo ago

line 98 in CanSortRecords.php

 protected function applyDefaultSortingToTableQuery(Builder $query): Builder
    {
        $sortColumnName = $this->getTable()->getDefaultSortColumn();

 protected function applyDefaultSortingToTableQuery(Builder $query): Builder
    {
        $sortColumnName = $this->getTable()->getDefaultSortColumn();

the $this->getTable() is known so why not prefix the defaultsortcolumn when $query builder has joins on it....

awcodes•3mo ago

But my point is if it just an integer, so what is the goal of sorting on that? What is the applicable use case of sorting on an id.

shenntek•3mo ago

nothing but the SQL is then ambiquous because the SQL has an innerjoin so the sort by should add the table prefix the result of $sortColumnName should not be by default "id" when joins happen

awcodes•3mo ago

Well, the use case doesn’t make sense to me, but maybe it is a bug. Feel free to submit an issue on the repo.

shenntek•3mo ago

that's why i added for now: ->defaultSort('products.id', 'desc') so it overrides that $sortColumName so that the SQL statement becomes correct when the Builder $query creates inner join

awcodes•3mo ago

Just seems to me that sorting on a product name, warehouse name or the timestamps would make more sense. 🙂

shenntek•3mo ago

okay but what if i actually dont want to sort anything... in the relationsmanager table

awcodes•3mo ago

Then don’t provide a default sort. Just let the records comeback as they are.

shenntek•3mo ago

yes precisely but then i get the sql error

awcodes•3mo ago

Like I said. Maybe it’s a bug.

shenntek•3mo ago

yes it is. 🙂

awcodes•3mo ago

Then submit an issue. 😄

shenntek•3mo ago

the ->defaultSort('products.id', 'desc') is a workaround 😄 just to create a correct query builder --> SQL will do, but i thought that you had the idea i was configuring the RelationManager wrong in this specific setup

awcodes•3mo ago

My initial thought was just that the relationship type was wrong.

shenntek•3mo ago

okay xD sorry my bad communication will create issue tomorrow with an example repo

awcodes•3mo ago

All good. Sorry if I’m just being dumb too. It’s hard to reason about the queries without actually being able to step through them. At least for me. 😅

shenntek•3mo ago

no absolutely not! no excuses plz. it's just hard to explain via text sometimes

awcodes•3mo ago

No doubt.

shenntek•3mo ago

Thank you for your time at least! i really appreciate it 🙂

awcodes•3mo ago

I appreciate you choosing filament.

shenntek•3mo ago

I also found the reason why the EditAction receives the wrong form input when duplicate column names in both tables are used. in filament/tables/src/Actions/EditAction.php there only is a check for the BelongsToMany relationship when pressing the save button. That is okay, but the same thing should be done in the setUp() function. but there it just converts the $record->attributesToArray() and when using the same columns names it just takes the first columns of the joined tables. in the $this->action function you can see that the pivot columns get renamed with a prefix of pivot_. which is a good thing not to have duplicates! so for example: 'quantity' becomes 'pivot_quantity' . and only those columns will get saved --> $pivotData = Arr::only($data, $pivotColumns); Will create ticket for that as well . but for now my workaround is that in my intermediate table i just make sure i dont use duplicate column names

Gaming

Programming

RelationManager belongsToMany withPivot(['id']) returns Column 'id' in order clause is ambiguous