here is my table
i want
product_id
that have both these conditions
`f_id` = '4' and `value` in ('10', '11', '12', '13' )
and
`f_id` = '10' and `value` in ('34')
which would be product_id : 11
here is my query
$products = Product::whereHas('ProductFilter' , function($q) use ($filter_groups){
foreach($filter_groups as $k=>$v)
$q->where('f_id' , $k )->whereIn( 'value' ,$v );
})->get();
which will result in this query
select * from `products` where exists
(
select * from `product_filters` where
`product_filters`.`product_id` = `products`.`id` and
`f_id` = '4' and `value` in ('10', '11', '12', '13' ) and
`f_id` = '10' and `value` in ('34')
)
but there is no relations between f_id
and value
and it wont return any result
basically what i want is this query
select * from `products` where exists
(
select * from `product_filters` where
`product_filters`.`product_id` = `products`.`id` and
`f_id` = '4' and `value` in ('10', '11', '12', '13' )
)
and exists
(
select * from `product_filters` where
`product_filters`.`product_id` = `products`.`id` and
`f_id` = '10' and `value` in ('34')
)
$query = Product::where([]);
foreach($filter_groups as $k=>$v)
$query->whereHas('ProductFilter' , function($q) use ($k , $v ){
$q->where('f_id' , $k )->whereIn( 'value' ,$v );
})
$products = $query->get();
you should write other condition as subquery
select * from `products` where exists
(
select * from `product_filters` where
`product_filters`.`product_id` = `products`.`id` and
(`f_id` = '4' and `value` in ('10', '11', '12', '13' ) ) and `product_filters`.`product_id` in (select product_id from product_filters where `f_id` = '10' and `value` in ('34') )
)