Battling Laravel's Sneaky DELETEs: How I Got ORDER BY and LIMIT to Play Nice with Joins

Hey folks, if you've ever stared at your terminal in horror as Laravel cheerfully deletes half a million rows instead of the tidy batch of 500 you asked for, you're not alone. That's exactly what kicked me into gear a few weeks back. As a Laravel enthusiast, I dove into the framework's MySQL grammar to sort out this quirky behavior. Spoiler: it ended in a merged PR and a few lessons in database drama. Let's break it down.

The Sneaky Problem

Picture this: You're cleaning up a massive cross_product_references table by joining it with products and nuking invalid entries in chunks. Your code looks solid-LIMIT(500), ORDER BY id, and a tidy while loop to keep things batched. But nope. Laravel's MySQL grammar? It silently strips the ORDER BY and LIMIT when JOINs are in the mix. Boom: one query, all 500k+ rows gone. Poof.

Here's the culprit in action (straight from my console command):

$batchSize = 500;
$totalDeleted = 0;

do {
    $deleted = DB::table('cross_product_references as cpr')
        ->leftJoin('products as p1', fn($join) => $join
            ->on('cpr.article', '=', 'p1.article')
            ->on('cpr.brand_id', '=', 'p1.brand_id'))
        ->leftJoin('products as p2', fn($join) => $join
            ->on('cpr.cross_article', '=', 'p2.article')
            ->on('cpr.cross_brand_id', '=', 'p2.brand_id'))
        ->where(fn($query) => $query
            ->whereNull('p1.id')
            ->orWhereNull('p2.id'))
        ->limit($batchSize)
        ->orderBy('cpr.id')
        ->delete();
    $totalDeleted += $deleted;
} while ($deleted > 0);

$this->line("Deleted <info>$totalDeleted</info> rows.");

Expected: Batches of 500, safe and sound. Actual: one massive, spectacular (and slightly horrifying) purge.
Turns out, this "feature" was there to dodge MySQL's native gripes about ORDER BY/LIMIT in DELETE...JOIN queries. But silent failures? Not cool.

The Fix (and a Side of Debate)

I fired up a PR #57138 overriding compileDeleteWithJoins in MySqlGrammar. Now? Laravel spits out the full SQL-JOINs, ORDER BY, LIMIT and all. Let the database (MySQL, MariaDB, or even Vitess) decide if it's happy. On vanilla MySQL? You'll get a crisp QueryException. Check mysql docs for more info (Mysql: Multi-Table Deletes). On PlanetScale or Vitess? It just works, thanks to their query-rewriting magic.

But oh, the comments! Taylor Otwell and Graham Campbell chimed in, and we had a mini philosophy sesh: Throw a framework exception for safety, or let MySQL yell? I pushed for explicit errors to avoid "surprise deletions," but we landed on compiling the full query anyway-best of both worlds. A quick branch swap to target master, some test tweaks, and... PR #57196 was born. Fast-forward a few days, and yeah, it got merged. 🎉

So, why does this actually matter? Predictability, my friend. You don't want your maintenance scripts nuking the database behind your back. If you're batch-deleting with joins, jump on the latest Laravel and double-check those LIMITs. Old-school MySQL folks, here's a hack: snag the IDs with FOR UPDATE, then do your DELETE WHERE IN. Works like a charm.

TL;DR

Laravel now respects ORDER BY and LIMIT in DELETE ... JOIN queries for MySQL. If the DB doesn't support it - you'll get a proper exception instead of a silent data wipe.

💡 PRs in question: #57138 (draft) and #57196

🚀 Merged into: Laravel 13.x

Grateful for the Laravel crew's sharp eyes-it was a reminder that open-source magic happens in those comment threads. Got a similar gotcha? Hit reply or ping me @tegos. What's your wildest query story?

Comments

[david duymelinck]

I find it strange the query builder has this problem. I always assumed that the query that I build in code will be in SQL.
Is this just another example of too much abstraction? Or a sign existing solutions don't factor in the current database landscape? Or both?

Over the past months I realized how much functionality and control we are losing by using abstractions on top of database query languages.

A few weeks ago I was dumbstruck by the new Postgres virtual columns feature, and fell from my chair learning that other databases already had that feature.
What are we doing creating virtual properties in models?
Even for the PHP 8.4 property hooks there is a virtual property example that I seen in many post announcing the feature.

class User implements Named
{

    public function __construct(private string $first, private string $last) {}

    public string $fullName {
        // Override the "read" action with arbitrary logic.
        get => $this->first . " " . $this->last;

        // Override the "write" action with arbitrary logic.
        set { 
            [$this->first, $this->last] = explode(' ', $value, 2);
        }
    }
}

You can just query it if you take advantage of the database features. And only create virtual fields if they require logic that is harder to achieve in SQL.

But after all of this I'm grateful you took the time to make it possible to prevent mistakes in future Laravel versions.

[Ivan Mykhavko]

Totally agree - abstractions can be double-edged. Laravel's query builder usually does mirror SQL closely, but sometimes safety checks or cross-DB quirks (like MySQL's strictness with DELETE ... JOIN ... LIMIT) cause it to "help a bit too much."
My goal was to close that gap so devs get full control and predictable behavior.
I just wanted to bridge that gap so devs aren't left guessing or fighting the framework-give folks actual control and results they can count on. Thanks for the thoughtful comment!