mysql - 如何在 db raw laravel 中添加条件？

Question

我的查询数据库原始 Laravel 像这样:

public function getTopProduct($price = null) {
    $products =  DB::select(DB::raw('SELECT * 
                    FROM (
                        SELECT a.*, b.name AS store_name, b.address
                        FROM products a
                        JOIN stores b ON b.id = a.store_id
                        WHERE a.status = 1                                                
                    ) AS product
                    GROUP BY store_id')
        );
    return $products;
}

我想添加条件

如果价格不为空，则会在 where 上添加条件价格

比如price=1000，那么就查询where像这样:

WHERE a.status = 1 AND a.price < 1000

如果价格 = null，则条件 AND a.price < 1000未执行

我该怎么做？

更新

我稍微改变了我的代码流程

我尝试这样:

public function getTopProduct($price)
{
    if($price == 1)
        $price_condition = 'WHERE price > 1000';
    else if($price == 2)
        $price_condition = 'WHERE price >= 500 AND a.price <= 1000';
    else if($price == 3)
        $price_condition = 'WHERE price < 500';
    else
        $price_condition = '';


    $products = DB::select('SELECT * 
                    FROM (
                        SELECT a.*, b.name AS store_name, b.address
                        FROM products a
                        JOIN stores b ON b.id = a.store_id
                        WHERE a.status = 1                                                
                    ) AS product
                    GROUP BY store_id
                    '.$price_condition
                );
    return $products;
}

而且它有效

你觉得怎么样？

我的解决方案是否正确？或者您有更好的解决方案？

Answer 1

public function getTopProduct($price = null) {
if($price==null){
         $products =  DB::select(DB::raw('SELECT * 
                FROM (
                    SELECT a.*, b.name AS store_name, b.address
                    FROM products a
                    JOIN stores b ON b.id = a.store_id
                    WHERE a.status = 1                                                
                ) AS product
                GROUP BY store_id')
    );
 }else{
       $products =  DB::select(DB::raw('SELECT * 
                FROM (
                    SELECT a.*, b.name AS store_name, b.address
                    FROM products a
                    JOIN stores b ON b.id = a.store_id
                    WHERE a.status = 1 AND a.price='.$price.'                                                
                ) AS product
                GROUP BY store_id')
    );
 }
 return $products;
}