Microsoft SQL exporting with filter fails with "A column has been specified more than once in the order by list"

[eugenefvdm]

When using the date filter with a backend of Microsoft SQL, and using the stock Filament Exporting Action, exports fail with the following message:

[2024-08-09 07:48:52] production.ERROR: SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique. (Connection: sqlsrv, SQL: select distinct top 1000 [consolidated_transactions].[id] from [consolidated_transactions] where [consolidated_transactions].[company_id] in (2) and ([Date] between 2024-07-26 00:00:00.000 and 2024-07-26 23:59:59.999) and [consolidated_transactions].[company_id] in (2) order by [consolidated_transactions].[id] asc, [id] asc) {"userId":2,"exception":"[object] (Illuminate\\Database\\QueryException(code: 42000): SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique. (Connection: sqlsrv, SQL: select distinct top 1000 [consolidated_transactions].[id] from [consolidated_transactions] where [consolidated_transactions].[company_id] in (2) and ([Date] between 2024-07-26 00:00:00.000 and 2024-07-26 23:59:59.999) and [consolidated_transactions].[company_id] in (2) order by [consolidated_transactions].[id] asc, [id] asc) at /home/forge/cloud-dev.willpowerpos.co.za/vendor/laravel/framework/src/Illuminate/Database/Connection.php:825)

If you examine the above query, the problematic area where a column (ID) is specified twice is:

order by [consolidated_transactions].[id] asc, [id] asc

Curiously, this also happens with Microsoft SQL for Laravel Nova: https://github.com/laravel/nova-issues/issues/2207

Note this doesn't fail on MySQL when exporting and a filter is applied.

[malzariey]

This is not related to the plugin

Just use ->reorder() before ordering your query