Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

TylerN86's avatar

Query builder delete query questions

Below is a working query that I am trying to recreate using the query builder in Laravel so that I can utilize the query bindings:

$query = 'delete b.* from locations_hierarchy as a join locations_hierarchy as b on b.descendent_id = a.descendent_id and b.id <> a.id where b.depth > 0 and a.ancestor_id in (' . implode(',', $existingSublocations) . ')'; 

$results = DB::delete($query);

An example of the final query is:

delete b.* from locations_hierarchy as a join locations_hierarchy as b on b.descendent_id = a.descendent_id and b.id <> a.id where b.depth > 0 and a.ancestor_id in (3,9)

My question is how can I recreate this raw query using the query builder? Is there a way to specify the table to delete from when using a self join? The resulting query is correct except for the table it deletes from:

DB::table('locations_hierarchy as a')->join('locations_hierarchy as b', function($join) {
                    $join->on('b.descendent_id', '=', 'a.descendent_id');
                    $join->on('b.id', '<>', 'a.id');
})->where('b.depth', '>', '0')->whereIn('a.ancestor_id', $sublocations)->delete();

Results in:

  "query" => "delete `a` from `locations_hierarchy` as `a` inner join `locations_hierarchy` as `b` on `b`.`descendent_id` = `a`.`descendent_id` and `b`.`id` <> `a`.`id` where `b`.`depth` > ? and `a`.`ancestor_id` in (?, ?)"
    "bindings" => array:3 [
      0 => "0"
      1 => "3"
      2 => "9"
    ]

If that is not possible is there any way to make the delete statement work with an array in the bindings parameter for the where in condition? I have tried this:

$query = 'delete b.* from locations_hierarchy as a join locations_hierarchy as b on b.descendent_id = a.descendent_id and b.id <> a.id where b.depth > 0 and a.ancestor_id in (:sublocations)'; 

DB::delete($query, ['sublocations' => $existingSublocations]);

Results in an array to string conversion error

0 likes
0 replies

Please or to participate in this conversation.