Delete rows with Laravel query builder and LEFT JOIN
Solution 1
It seems that my way is not possible. So, I did it like this.
$q = 'DELETE deadline, job FROM deadline LEFT JOIN job ...where deadline.id = ?';
$status = \DB::delete($q, array($id));
Documentation: http://laravel.com/docs/database#running-queries
Solution 2
DB::table(DB::raw('deadline, job'))
might work. If it doesn't, you'll have to write the SQL manually and call it via DB::statement()
.
Solution 3
To make laravel allow a join in a delete is simple - you just need to change the compileDelete function in Illuminate\Database\Query\Grammars\Grammar
to this:
public function compileDelete(Builder $query)
{
$table = $this->wrapTable($query->from);
$components = implode(' ', array(
is_array($query->joins) ? $this->compileJoins($query, $query->joins) : '',
is_array($query->wheres) ? $this->compileWheres($query, $query->wheres) : '',
is_array($query->limit) ? $this->compilelimit($query, $query->limit) : '',
is_array($query->offset) ? $this->compileOffset($query, $query->offset) : ''
));
return trim("delete $table from $table ".$components);
}
Then ->delete() will work the way you expect it to. I've already added this as a pull request to the laravel framework repo, so hopefully this might be merged into the next version - just have to see.
tasmaniski
Updated on June 04, 2022Comments
-
tasmaniski almost 2 years
How to delete rows from multiple tables in one query (with left join). The query:
DELETE `deadline`, `job` FROM `deadline` LEFT JOIN `job` ....
So, I try it like this:
DB::table('deadline', 'job') ->leftJoin('job', 'deadline.id', '=', 'job.deadline_id') ->where('deadline.id', $id) ->delete();
Seems that Laravel doesn't support delete from multiple tables with left join.
Is there a supported way or workaround?