如何将条件聚合mysql转换为laravel查询? [英] How can I convert conditional aggregation mysql to laravel query?

问题描述

我的sql查询如下:

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

推荐答案

您必须在Haves子句中提供aggregate函数，我们可以像这样重用我们在选择中使用的同一个函数

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查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！