如何将条件聚合 mysql 转换为 laravel 查询?

Question

我的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'

如果查询已执行，则有效

我想将查询 sql 转换为 laravel 查询

我尝试这样：

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

当查询执行时，存在如下错误：

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
)

更新

我找到了解决方案。它像这样：

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

Answer 1

您必须在 having 子句中提供aggregate函数，我们可以像这样重用我们在 select 中拥有的相同函数

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

编辑：评论后

您可以像这样为每个参数执行

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

Answer 2

希望对您有所帮助 -

$perPage = $request->input("per_page", 10);
$page = $request->input("page", 1);
$skip = $page * $perPage;
if($take < 1) { $take = 1; }
if($skip < 0) { $skip = 0; }
$basicQuery =DB::select(DB::raw("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'"
));
$totalCount = $basicQuery->count();
$results = $basicQuery
->take($perPage) // can be 10
->skip($skip)
->get();
$paginator = new IlluminatePaginationLengthAwarePaginator($results,     $totalCount, $take, $page);
return $paginator;