Unsupported Order By on Update and Delete Queries for Notification Database
I am using the SingleStore database drive and it does not support ORDER BY when using UPDATE or DELETE. It happens when the
Livewire/DatabaseNotifications functions call getNotificationsQuery, but when updating or deleting a notification the following error is returned:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by created_at desc' at line 1
DELETE FROM
notifications
WHERE
notifications.notifiable_type = 'App\Models\USER'
AND notifications.notifiable_id = 1
AND notifications.notifiable_id IS NOT NULL
AND JSON_EXTRACT_STRING(data, 'format') = 'filament'
AND id = 'e78e0963-1f8a-4bee-b6ac-c204151226c9'
ORDER BY
created_at DESC
Before creating a bug report or a pull request, I would like to check if there is a simple solution for it or would I have to add reorder() to the removeNotification, markNotificationAsRead, markNotificationAsUnread and clearNotifications and markAllNotificationsAsRead functions of Livewire/DatabaseNotifications and I would like to know if this is really valid, as it already would resolve this error for any other database that does not have this support.11 Replies
Why is there an order by on a delete?
Order by is inserted when creating the standard Laravel query when Filament calls $this->getUser()->notifications():
And this same function is called in the change and delete functions.
This function is Filament\Notifications\Livewire\DatabaseNotifications
Here is the notifications function from Laravel trait. The latest() is called and insert the order by into the query
Ok, but where is order by in this?
Delete takes an id and deletes that id on the table.
Just trying to understand the use case.
Ah ok. In the notification panel when I try to delete or mark as read any notification.
Hmm. That’s odd.
It only works fine when call reorder() before delete or mark as read the notifications.
As
reorder() clean the $orderbyThere is already a issue on singlestore driver no manage these: https://github.com/singlestore-labs/singlestoredb-laravel-driver/issues/84
GitHub
SingleStore does not allow a order by in a delete query · Issue #84...
We are in the process of migrating to Singlestore and have encountered a compatibility issue with Laravel's Database Notifications. The issue arises from the HasDatabaseNotifications trait, whi...