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.