Laravel 4: Where Not Exists

Question

I need my model to return only those records from one table where a matching record does not exist in another table. I was thinking that the solution might be with Query Scopes but the documentation only scratches the surface. So my SQL would look something like this:

SELECT *
FROM A
WHERE NOT EXISTS (SELECT A_id FROM B
                WHERE B.A_id = A.id)

Here are my tables:

A
-------------
| id | name |
-------------

B
--------------------
| id | A_id | name |
--------------------

Probably unnecessary but here are my models. Model for A:

class A extends Eloquent{

    public function B(){
        return $this->hasOne('B', 'A_id', 'id');
    }
}

Model for B:

class B extends Eloquent{

    public function A(){
        return $this->belongsTo('B', 'A_id', 'id');
    }
}

marcanuy · Accepted Answer · 2014-03-14 16:26:51Z

15

Something like

A::whereNotExists(function($query)
            {
                $query->select(DB::raw(1))
                      ->from('B')
                      ->whereRaw('A.id = B.id');
            })
            ->get();

answered Mar 14, 2014 at 16:26

marcanuy

24.2k10 gold badges68 silver badges118 bronze badges

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

11 Comments

sterfry68 Over a year ago

I need the opposite of the has method. (Is there a hasNot() method?)

sterfry68 Over a year ago

I did find the whereNotExists method and I'm working with that now. But I'm finding a problem: In the whereRaw I have to use the entire table name (prefix and all) I'd don't want my prefix to be found anywhere in my code.

sterfry68 Over a year ago

Also, I'm guessing that your code above should be in the Controller. Is there a way to do it in the Model?

marcanuy Over a year ago

You can put it in a query scope method containing: return static::whereNotExists(function($query) { $query->select(DB::raw(1)) ->from('B') ->whereRaw('A.id = B.id'); }) ->get();

sterfry68 Over a year ago

This worked:

scopeName($query){return $query->whereNotExists(function($query){$this_table = DB::getTablePrefix() . $this->table;  $query->select(DB::raw('A_id'))  ->from('B')   ->whereRaw('A_id = '.$this_table.'.id');  })}

Thank you

|

Collectives™ on Stack Overflow

Laravel 4: Where Not Exists

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related