如何在db raw laravel中添加条件?
我查询数据库原始laravel这样的:如何在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
例如条件,涨价= 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;
}
和它的作品
你怎么看?
我的解决方案是否正确?或者你有更好的解决方案?
尝试类似下面的代码与where
条件:
$price = array_column($request->get('price'), 'id');
OR
$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();
希望这可以帮助你!
如果使用'DB:raw'会怎么样? –
它似乎没有帮助我。试试看看我的情况。选择,from,group by在db:raw内部。我想在db里添加条件:raw –
你可以在DB :: raw中做到这一点!将您的DB :: raw代码放在我的查询中,然后尝试!我认为这是工作。 –
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;
}
是否可以在'DB:raw'内添加条件?因为你的代码这么久 –
是的,这将是SQL条件不是PHP的条件 –
您可以使用Advanced Join Clauses在查询生成器
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();
如何添加条件,如果它使用'DB:raw'就像我的情况? –
取一个变量,'$价格= array_column($请求 - >获取( '价格'));'和'做 - > when'用'where'条件查询!它在价格要求时执行! –