Eloquent delete does not respect limit if join is part of the query

[SajtiDH]

Laravel Version

12.19.3

PHP Version

8.4

Database Driver & Version

No response

Description

It seems like the delete() not always respect limit().

Steps To Reproduce

Example:

User::where('type', 'standard')->limit(5)->delete(); //deletes 5 users

User::join('address', 'user.address_id', '=', 'address.id')->where('user.type', 'standard')->limit(5)->delete(); //deletes all users matching the where condition

[achyutkneupane]

LIMIT doesn't work with multi-table delete. The driver basically confuses between the table from where the data should be deleted.

Instead you can do:

$ids = User::join('address', 'user.address_id', '=', 'address.id')
    ->where('user.type', 'standard')
    ->limit(5)
    ->pluck('user.id');

User::whereIn('id', $ids)->delete();

[SajtiDH]

Shouldn't it be prevented then? Throw an error or something? (It seems like this is not even mentioned in the documentation)

[achyutkneupane]

Shouldn't it be prevented then? Throw an error or something? (It seems like this is not even mentioned in the documentation)

The issue is from the database driver. So, it may not be prevented in framework level. Also, preventing it in framework level has a possibility of breaking existing applications.

And since it is a community-based framework, you can submit a PR in https://github.com/laravel/docs and mention it there.

[rodrigopedra]

I am not sure what @achyutkneupane means by an issue on the "database driver" (which driver? PHP's PDO? Laravel's Database Driver?).

But if you are using MySQL as your DBMS, it doesn't allow LIMIT with multiple-table DELETE.

You cannot use ORDER BY or LIMIT in a multiple-table DELETE.

Source: https://dev.mysql.com/doc/refman/8.0/en/delete.html#id266747

I am not sure if this limitation is present in other DBMS.

[achyutkneupane]

I am not sure what @achyutkneupane means by an issue on the "database driver" (which driver? PHP's PDO? Laravel's Database Driver?).

But if you are using MySQL as your DBMS, it doesn't allow LIMIT with multiple-table DELETE.

You cannot use ORDER BY or LIMIT in a multiple-table DELETE.

Source: https://dev.mysql.com/doc/refman/8.0/en/delete.html#id266747

I am not sure if this limitation is present in other DBMS.

I was referring to the driver key in config/database.php just to keep it relatable. Should’ve clarified that better. Thanks for pointing it out!

[emul-docler]

Are you suggesting that the query builder should check if the driver is MySQL, and if a LIMIT is used in a multi-table DELETE, it should throw an exception?

I'm not quite sure what you mean by "the driver basically confuses between the table from where the data should be deleted".

Was the part about LIMIT removed intentionally? I'm asking because MySQL already throws an error when such a query is executed. So, wouldn't the safest approach be to simply attempt execution and let MySQL handle it?