How to query using whereBetween() from selected DB::raw() value

Question

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?

Answer 1

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

Online Laravel query builder test

Answer 2

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();
```