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
Please or to participate in this conversation.