join - whereNotIn

I have 2 tables:

Members
id | group
1   | 1
2  | 1
3  | 2


Signees
id| member_id | group
1  | 1                  | 1
1  | 2                 | 1

What i'm looking to do, is to get a collection / array of all the members, that ARE NOT a signee, so in the above scenario, the member with id = 3 would be the only item returned

Is there an easy way of doing this without looping through 2 collections?

Thanks

sujancse

9 years ago

Level 10

You can do something like this which is a nested where clause

App\Member::whereNotIn('members.group', function($q){
    $q->selectRaw("signees.group from signees");
})
->get();

This will return exactly what are you looking for.

craigwillis85

9 years ago

Level 1

Something like the following in SQL:

SELECT id      
FROM   members
WHERE  members.group_id = 'XXX'
and members.id
       NOT IN 
       ( SELECT signees.member_id
         FROM   signees
         where signees.url_id = 'ZZZ'
       );

craigwillis85

9 years ago

Level 1

I've just used a raw SQL query, which seems to have worked

Thanks!

1 like

Please or to participate in this conversation.