Tengo un resultado de consulta con el resultado sin procesar de la base de datos seleccionado y necesito agregar otra cláusula "where between" a esa consulta con el resultado sin procesar de la base de datos seleccionado. mi consulta es
$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(); Necesito agregar ->whereBetween('rate', [4, 5]) así a mi consulta. ¿Cómo puedo hacer eso?
Debe usar ->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();Si whereBetween('rate', [4, 5]) no funciona, creo que podrías hacer una de las siguientes cosas:
Podrías usar una subconsulta
$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();Filtrar después de obtener los resultados
$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(); O use DB::raw como el primer argumento de 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(); ```