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

Issue

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?

Solution

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

Answered By - Slava Rozhnev