eloquent where not in query?

Question

I'm trying to build the following sql query with eloquent. The query gives me all records from table_a which are in the list of ids and do not appear in table_b.

select * from table_a 
where id in (1,2,3)
   and id not in 
      (select tablea_id from table_b 
       where tablea_id in (1,2,3))

So how do I do it in eloquent ? I want to avoid using a raw query.

//does not work
TableA::whereIn('id',$ids)
   ->whereNotIn('id', TableB::select('tabla_id')->whereIn($ids));

possible duplicate of [Laravel Eloquent "WHERE NOT IN"](http://stackoverflow.com/questions/25849015/laravel-eloquent-where-not-in) — toesslab, Apr 15 '15 at 06:58
@lukasgeiter OOpps, so same for me (re)read th F* manual... Thx for pointig at! — toesslab, Apr 15 '15 at 07:05

lukasgeiter · Accepted Answer · 2015-04-15T07:06:14.223

11

To run a subquery you have to pass a closure:

TableA::whereIn('id',$ids)
      ->whereNotIn('id', function($q){
          $q->select('tabla_id')
            ->from('tableb');
            // more where conditions
      })
      ->get();

edited Apr 15 '15 at 07:06

answered Apr 15 '15 at 07:02

lukasgeiter

147,337
26
332
270

@pc-shooter Whoops thanks. Feel free to edit such obvious typos yourself next time ;) – lukasgeiter Apr 15 '15 at 07:06
Tought that I couldn't just edit one character in other users posts? – toesslab Apr 15 '15 at 07:07
Ah I see... I'm over that rep limit so I forgot that for a second – lukasgeiter Apr 15 '15 at 07:08
It's fine like this, there would be a lot "just-code-formatting-or-one-two-chars-editing" – toesslab Apr 15 '15 at 07:09

eloquent where not in query?

1 Answers1