我的查询数据库原始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;
}


我想补充条件

如果price不为null,则会在where上添加条件价格

例如,price = 1000,然后在where上进行如下查询:

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


如果price = 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;
}


而且有效

你怎么想?

我的解决方案是否正确?还是您有更好的解决方案?

最佳答案

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;
}

关于mysql - 如何在db raw laravel中添加条件?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/46558989/

10-16 20:35