John Davidson

Message:

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 \Illuminate\Database\Query\Builder $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);

}

---

My Answer:
>images()->where('category', $category)->whereNotIn('id', function($query) {
$query->select('image_id')->from('excluded_images');
})->get();