Laravel 5.5 - whereIn and whereNotIn in same eloquent query?

Question

I have two tables where I need to pluck the valid building ids (those that match the $buildingIds array) that exist in buildings AND must ensure they don't already exist in buildings_built (so we know which buildings need to be built). It can be done in two queries easily, but I am trying to do it with a single query to be more efficient:

$buildingIds = ['a1','b2','c3'];

Tables
// buildings       (id, building_name)    (a1, adam)  (b2, barney)  (c3, castor) 
// buildings_built (id, building_id)       (1, a1)     (2, b2)

Here is my attempt at this that doesn't work properly:

$buildingsToBuildFromIdsArray = Buildings::whereIn('buildings.id', $ids)
                                 ->whereNotIn('buildings_built.building_id', $ids)
                                 ->pluck('buildings.id');

Ideally, the query should return ['c3'], since that building exists in buildings table and does not exist in buildings_built table (has not been built yet).

Any idea how to get it to work correctly?

Answer 1

What you could do is to create a relationship between two models - I will work with the default naming for models as Building and Build:

class Building extends Model
{
    public function builds(): HasMany
    {
        return $this->hasMany(Build::class, 'building_id', 'id');
    }
}

class Build extends Model
{
    public function building(): BelongsTo
    {
        return $this->belongsTo(Building::class, 'id', 'building_id');
    }
}

To fetch your records you could then use the doesntHave query builter method

Building::whereIn('id', $ids)->doesntHave('builds')->pluck('id');