I have a query result with the selected DB raw result and I need to add another where between clause to that query with the selected DB raw result. my query is
$products = Product::leftJoin('product_reviews', 'product_reviews.product_id', '=', 'products.id')
->select(
'products.*',
DB::raw("IF(SUM(product_reviews.star_rating) != 0, SUM(product_reviews.star_rating) / COUNT(product_reviews.id), 0) AS rate")
)
->get();
I need to add ->whereBetween('rate', [4, 5]) like this to my query. How can I do that?
You must use ->havingBetween('rate', [4, 5])
$products = Product::leftJoin('product_reviews', 'product_reviews.product_id', '=', 'products.id')
->select(
'products.id',
DB::raw("IF(SUM(product_reviews.star_rating) != 0, SUM(product_reviews.star_rating) / COUNT(product_reviews.id), 0) AS rate")
)
->groupBy('products.id')
->havingBetween('rate', [4, 5])
->get();
If whereBetween('rate', [4, 5]) doesn't work, I think you could do one of the following:
You could use a subquery
$sub = DB::query()
->select(
'products.*',
DB::raw("IF(SUM(product_reviews.star_rating) != 0, SUM(product_reviews.star_rating) / COUNT(product_reviews.id), 0) AS rate")
)
->from('products')
->leftJoin('product_reviews', 'product_reviews.product_id', '=', 'products.id');
$products = Product::query() // using Product:: instead of DB:: to cast every result to a model.
->fromSub($sub, 'products')
->whereBetween('rate', [4, 5]);
->get();
Filter after getting the results
$products = Product::query()
->select(
'products.*',
DB::raw("IF(SUM(product_reviews.star_rating) != 0, SUM(product_reviews.star_rating) / COUNT(product_reviews.id), 0) AS rate")
)
->leftJoin('product_reviews', 'product_reviews.product_id', '=', 'products.id')
->cursor()
->filter(fn($product) => $product->rate >= 4 && $product->rate <= 5)
->values()
->collect();
Or use DB::raw as the first argument of whereBetween
$products = Product::query()
->select(
'products.*',
DB::raw("IF(SUM(product_reviews.star_rating) != 0, SUM(product_reviews.star_rating) / COUNT(product_reviews.id), 0) AS rate")
)
->whereBetween(
DB::raw('IF(SUM(product_reviews.star_rating) != 0, SUM(product_reviews.star_rating) / COUNT(product_reviews.id), 0)'),
[4, 5]
)
->leftJoin('product_reviews', 'product_reviews.product_id', '=', 'products.id')
->get();
```