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

11dmarpk 于 2022-12-05 发布在其他

关注(0)|答案(3)|浏览(188)

我的查询db raw 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

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

而且很管用
你觉得如何？
我的解决方案是否正确？或者你有更好的解决方案？

laravel

来源：https://stackoverflow.com/questions/46558989/how-can-i-add-condition-in-db-raw-laravel

3条答案

按热度按时间

sbdsn5lh1#

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

赞(0）回复(0）举报 2022-12-05

xxhby3vn2#

您可以在查询构建器中使用高级连接子句

DB::table('products')
        ->join('stores', function ($join) use ($price) {
            $query = $join->on('stores.id', '=', 'products.store_id')
                          ->where('products.status', '=', 1);

            if (!empty($price)) {
                $query->where('products.price', '<', 1000);
            }
        })
        ->select('products.*', 'stores.name AS store_name', 'stores.address')
        ->groupBy('products.store_id')
        ->get();

赞(0）回复(0）举报 2022-12-05

frebpwbc3#

在where条件下尝试类似下面的代码：

$price = array_column($request->get('price'), 'id');

或

$price = $request->get('price');

$query = 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'))
             ->when($price == 1, function($query) use ($price) {
                  $query->where('price', '>', '1000');
             })
             ->when($price == 2, function($query) use ($price) {
                  $query->where('price', '>=', '500')
                        ->where('a.price', '<=', '1000');
             })
             ->when($price == 3, function($query) use ($price) {
                  $query->where('price', '<', '500');
             })
             ->when($price == '', function($query) use ($price) {
                  $query->where('your_where_when_price_empty');
             })
             ->get();

希望这对你有帮助！

赞(0）回复(0）举报 2022-12-05

我来回答

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

3条答案

相关问题

热门标签

最新问答