How can I convert conditional aggregation mysql to laravel query?

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

I want to convert the query sql to laravel query

I try like this :

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


How can I solve the error?

Note

The result of echo '<pre>';print_r($param);echo '</pre>';die(); :

echo '<pre>';print_r($param);echo '</pre>';die();

Array
(
[brand] => honda
[model] => pcx
[category] => test1
[subcategory] => test2
)


Update

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;



2 Answers
2

You have to supply the aggregate function in having clause, we can reuse the same one that we have in select like this

aggregate

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


EDIT: After comments

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;


SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '} = ?' at line 1 (SQL: select count(*) as aggregate from

a

attr_maps

b

b

number

a

number

a

number

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'brand' in 'having clause' (SQL: select count(*) as aggregate from

a

attr_maps

b

b

no

a

no

a

no

$$key

havingRaw

Hope this helps you -

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


Call to a member function paginate() on array

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.