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
LeandroFerreira2mo ago
you don't need to add 'id' in the withPivot..
shenntek
shenntek2mo ago
or shouldnt i create an "id" column at the pivot table? and does laravel this automagically?
LeandroFerreira
LeandroFerreira2mo ago
$table->id(); + allowDuplicates() try this without 'id' in the withPivot..
shenntek
shenntek2mo 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
LeandroFerreira2mo ago
migration, as you did...
shenntek
shenntek2mo 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
LeandroFerreira2mo 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
shenntek2mo 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`
or
ORDER BY `product_warehouse`.`id`
ORDER BY `product_warehouse`.`id`
awcodes
awcodes2mo 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
shenntek2mo 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
awcodes2mo ago
Many to many most definitely requires a pivot table.
shenntek
shenntek2mo 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
awcodes2mo 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
shenntek2mo ago
@awcodes that's why ->withPivot([]) on models... but if the names are the same in all tables you have a problem
awcodes
awcodes2mo ago
Well, yea, but that’s not necessarily a filament issue. That’s an issue of how you’re doing the joins.
shenntek
shenntek2mo ago
yes that i can agee on supplement columns like quantity, but not on the primary keys id
awcodes
awcodes2mo ago
Right but the relationship should handle the keys. The pivot attributes should never need the primary keys
shenntek
shenntek2mo 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
awcodes2mo ago
Sorting on an id is always going to create problems.
awcodes
awcodes2mo ago
You’re going to have to redefine any pivot relationship data that uses the same keys.
shenntek
shenntek2mo ago
yes i will do that for quantity etc... but for the id it should be handled by laravel/filament correctly
awcodes
awcodes2mo ago
It’s no different. Filament / Laravel shouldn’t do any of that automatically.
shenntek
shenntek2mo 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
awcodes2mo 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
shenntek2mo 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
awcodes2mo 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
shenntek2mo ago
that's why i added the id on the pivot table itself...
awcodes
awcodes2mo 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
shenntek2mo 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
awcodes2mo 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
shenntek2mo 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
awcodes2mo 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
shenntek2mo 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
awcodes2mo ago
Just seems to me that sorting on a product name, warehouse name or the timestamps would make more sense. 🙂
shenntek
shenntek2mo ago
okay but what if i actually dont want to sort anything... in the relationsmanager table
awcodes
awcodes2mo ago
Then don’t provide a default sort. Just let the records comeback as they are.
shenntek
shenntek2mo ago
yes precisely but then i get the sql error
awcodes
awcodes2mo ago
Like I said. Maybe it’s a bug.
shenntek
shenntek2mo ago
yes it is. 🙂
awcodes
awcodes2mo ago
Then submit an issue. 😄
shenntek
shenntek2mo 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
awcodes2mo ago
My initial thought was just that the relationship type was wrong.
shenntek
shenntek2mo ago
okay xD sorry my bad communication will create issue tomorrow with an example repo
awcodes
awcodes2mo 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
shenntek2mo ago
no absolutely not! no excuses plz. it's just hard to explain via text sometimes
awcodes
awcodes2mo ago
No doubt.
shenntek
shenntek2mo ago
Thank you for your time at least! i really appreciate it 🙂
awcodes
awcodes2mo ago
I appreciate you choosing filament.
shenntek
shenntek2mo 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
Want results from more Discord servers?
Add your server
More Posts