Laravel: Reverse WHERE NOT IN - exclude any parent table that has children of id=XXX

Scenario is this: Need to get all sites which don't have licensee ID = XXX.

  $data = DB::select("SELECT  * FROM sites AS s
              WHERE status = 'Existing Site' AND
              $sharerId NOT IN (SELECT organisation_id
                      FROM licensees AS l
                        WHERE site_id = s.id)
                    order by siteName;") ;

This code does that fine.

But I get an array, which I can't filter on later, just in case the user wants to search for site name YYY.

$sharerId has the ID to exclude from the sites.

 $data = $this->select($select)
 ->where('status', 'Existing Site')
 ->whereNotIn($sharerId,
 Licensee::where('site_id', DB::raw('sites.id'))
 )->get() ;

Tried this, but I really didn't expect it to work, and it didn't :-P

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/34hQYVm
via IFTTT