本文介绍了如何将条件聚合 mysql 转换为 Laravel 查询?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的 sql 查询如下:
My sql query like this :
SELECT a.number, a.description,
MAX(CASE WHEN b.attribute_code = 'brand' then b.attribute_value END) as brand,
MAX(CASE WHEN b.attribute_code = 'model' then b.attribute_value END) as model,
MAX(CASE WHEN b.attribute_code = 'category' then b.attribute_value END) as category,
MAX(CASE WHEN b.attribute_code = 'subcategory' then b.attribute_value END) as subcategory
FROM items a JOIN
attr_maps b
ON b.number = a.number
GROUP BY a.number, a.description
HAVING brand = 'honda'
如果查询执行，它就起作用
If the query executed, it works
我想把查询sql转为laravel查询
I want to convert the query sql to laravel query
我是这样尝试的:
$query = Item::selectRaw("a.number, a.description, MAX(CASE WHEN b.attribute_code = 'brand' then b.attribute_value END) as brand, MAX(CASE WHEN b.attribute_code = 'model' then b.attribute_value END) as model, MAX(CASE WHEN b.attribute_code = 'category' then b.attribute_value END) as category, MAX(CASE WHEN b.attribute_code = 'subcategory' then b.attribute_value END) as subcategory")
->from('items as a')
->join('attr_maps as b','b.number','=','a.number')
->groupBy('a.number');
foreach($param as $key => $value) {
$query = $query->havingRaw("$key = $value");
}
$query = $query->orderBy('description')
->paginate(10);
return $query;
查询执行，存在这样的错误:
It the query executed, there exist error like this :
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'brand' in 'having clause' (SQL: select count(*) as aggregate from `items` as `a` inner join `attr_maps` as `b` on `b`.`no` = `a`.`no` group by `a`.`no` having brand = honda)
我该如何解决错误?
注意
echo '<pre>';print_r($param);echo '</pre>';die();的结果:
Array
(
[brand] => honda
[model] => pcx
[category] => test1
[subcategory] => test2
)
更新
我找到了解决方案.像这样:
I had find a solution. It like this :
public function list($param)
{
$brand = "MAX(CASE WHEN b.attribute_code = 'brand' then b.attribute_value END)";
$model = "MAX(CASE WHEN b.attribute_code = 'model' then b.attribute_value END)";
$category = "MAX(CASE WHEN b.attribute_code = 'category' then b.attribute_value END)";
$subcategory = "MAX(CASE WHEN b.attribute_code = 'subcategory' then b.attribute_value END)";
$query = Item::selectRaw("a.number, a.description, {$brand} as brand, {$model} as model, {$category} as category, {$subcategory} as subcategory")
->from('items as a')
->join('item_attr_maps as b','b.number','=','a.number')
->groupBy('a.number');
foreach($param as $key => $value) {
$query = $query->havingRaw("{$$key} = ?", [$value]);
}
$query = $query->orderBy('description')
->paginate(self::ITEM_PER_PAGE);
return $query;
}
推荐答案
你必须在 have 子句中提供 aggregate 函数，我们可以像这样重用我们在 select 中的同一个函数>
You have to supply the aggregate function in having clause, we can reuse the same one that we have in select like this
$brand = "MAX(CASE WHEN b.attribute_code = 'brand' then b.attribute_value END)";
$model = "MAX(CASE WHEN b.attribute_code = 'model' then b.attribute_value END)";
$category = "MAX(CASE WHEN b.attribute_code = 'category' then b.attribute_value END)";
$subcategory = "MAX(CASE WHEN b.attribute_code = 'subcategory' then b.attribute_value END)";
$brandName = 'honda';
$query = Item::selectRaw("a.number, a.description, {$brand} as brand, {$model} as model, {$category} as category, {$subcategory} as subcategory")
->from('items as a')
->join('attr_maps as b','b.number','=','a.number')
->groupBy('a.number')
->havingRaw("{$brand} = ?", [$brandName])
->orderBy('description')
->paginate(10);
return $query;
评论后
你可以像这样对每个参数执行
You can execute for each params like this
$query = Item::selectRaw("a.number, a.description, {$brand} as brand, {$model} as model, {$category} as category, {$subcategory} as subcategory")
->from('items as a')
->join('attr_maps as b','b.number','=','a.number')
->groupBy('a.number')
->orderBy('description');
foreach($param as $key => $value) {
$query = $query->havingRaw("{$$key} = ?", [$value]);
}
$results = $query->paginate(10);
return $results;
这篇关于如何将条件聚合 mysql 转换为 Laravel 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！