Skip to content
Advertisement

Eloquent relationship where not in sub-query

I have got a relationship where I select all row’s based on the category, however I need to exclude some of these if they are within a sub query.

/** @var IlluminateDatabaseQueryBuilder $images */
$images = $vehicle->images()
    ->whereIn('image_category', $website->image_categories)
    ->orderBy('seq', 'ASC');

$images->whereNotIn('id', static function ($q) {
    return $q->select('id')
        ->whereIn('image_category', [0, 99])
        ->groupBy('seq')
        ->having(DB::raw('count(`seq`)'), '>', 1);
});

dd($images->toSql(), $images->getBindings());

So above is my code, nearly works as I want it, however it seems that the $q variable doesn’t have the table name within the query, below is the query outputted:

select
    *
from
    `vehicle_images`
where
    `vehicle_images`.`vehicle_id` = ?
    and `vehicle_images`.`vehicle_id` is not null
    and `image_category` in (?, ?)
    and `id` not in (
        select
            `id`
        where
            `image_category` in (?, ?)
        group by
            `seq`
        having
            count(`seq`) > ?
    )
order by
    `seq` asc

This is the relationship:

public function images()
{
    return $this->hasMany(VehicleImage::class);
}

Advertisement

Answer

You can specify what table you want to use.

$images->whereNotIn('id', static function ($q) {
    return $q->select('id')->from('{CORRECT_TABLE_NAME_HERE}')
        ->whereIn('image_category', [0, 99])
        ->groupBy('seq')
        ->having(DB::raw('count(`seq`)'), '>', 1);
});

I don’t know what exactly the table name should be, hence the placeholder.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement