John Davidson

php - Eloquent relationship where not in sub-query

0 comments
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();

Rate this post

3 of 5 based on 6487 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us