unwanted "order by" in modifyQueryUsing - how to disable it?
Hi,
When I use modifyQueryUsing() to query SQL it sticks an "ORDER BY" which causes an error in mysql strict mode. When I remove the ORDER BY, the query executes correctly in phpmyadmin. How do I disable the sorting that sticks to the query?
My code:
return $table
->modifyQueryUsing(
function (Builder $query) {
$query->Select('product_id')->where('order_id', $this->ownerRecord->id)
->where('is_complex', 0)->groupBy('product_id')->withoutGlobalScopes();
}
Solution:Jump to solution
Thank you for your suggestions. I solved it with 2 steps:
1. forcing sort ->defaultSort('product_id')
2. after Error "getTableRecordKey(): Return value must be of type string, null returned", changed key:
`
public function getTableRecordKey(Model $record): string...
6 Replies
Does your table have a
defaultSortOrder() defined on it?
Or do you have multiple filters/sorts defined?
Also, whenever you add GroupBy logic to a query, you must also update other affected things, such as the Selected fields and the OrderBy and Having clauses, if any.
So, perhaps the answer to your OrderBy issue is not in removing the OrderBy, but in updating the OrderBy to something else, or incorporating the ordered field into your select and groupby query portions.I don't have filters or default sortings here. As I wrote, I also made sure the query works correctly with sql mode strict (the query works correctly in phpmyadmin when you remove the sorting part). It seems to me that Filament sticks the default sorting by ID which in this case is the problem.
This is where it's helpful to see your actual code.
I think if one of your columns has
->sortable() on it, it auto-adds a sort order.Solve it with : $query = $query->reorder();
Solution
Thank you for your suggestions. I solved it with 2 steps:
1. forcing sort ->defaultSort('product_id')
2. after Error "getTableRecordKey(): Return value must be of type string, null returned", changed key:
public function getTableRecordKey(Model $record): string
{
return $record->product_id;
}
I didn't have such a column, but this hint guided me to force sort by ->defaultSort() which helped