Delete rows with Laravel query builder and LEFT JOIN

Question

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?

Not sure if this is a new feature in Eloquent but it works now. I was able to delete with a left joined query. — Matt K
– Matt K, Commented Jun 22, 2017 at 16:00

tasmaniski · Accepted Answer · 2014-06-03 08:51:20Z

6

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

answered Jun 3, 2014 at 8:51

tasmaniski

4,9083 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andreas · Accepted Answer · 2014-05-22 11:57:54Z

1

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().

answered May 22, 2014 at 11:57

Andreas

8,0692 gold badges32 silver badges38 bronze badges

Comments

Benubird · Accepted Answer · 2014-09-04 17:31:17Z

1

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.

answered Sep 4, 2014 at 17:31

Benubird

19.7k28 gold badges97 silver badges148 bronze badges

2 Comments

Umair Ahmed Over a year ago

Are there any plans of such change to be implemented in Laravel? Cuz its really a shame, and on top no mention of such in docs nor does it throw an exception.

luukvhoudt Over a year ago

They implemented this feature, view it here. However this compiles in case of the OP to the following SQL:

delete from "deadline" where "rowid" in (select "deadline"."rowid" from "deadline" left join "job" on "deadline"."id" = "job"."deadline_id" where "deadline"."id" = ?)

assuming that only the first argument for table() is applied.

linktoahref · Accepted Answer · 2017-11-28 05:31:01Z

0

$query = 'DELETE courses,course_contents FROM courses 
          INNER JOIN course_contents ON course_contents.course_id = courses.id  
          WHERE courses.id = ?';

\DB::delete($query, array($id));

edited Nov 28, 2017 at 5:31

linktoahref

8,0273 gold badges32 silver badges54 bronze badges

answered Oct 17, 2017 at 2:57

Tula Hean

212 bronze badges

Collectives™ on Stack Overflow

Delete rows with Laravel query builder and LEFT JOIN

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related