How can I convert conditional aggregation mysql to laravel query?

Question

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(); :

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

rkj · Accepted Answer · 2018-09-09T08:13:45.207

2

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;

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;

edited Sep 09 '18 at 08:13

answered Sep 09 '18 at 05:47

rkj

8,067
2
27
33

There exist error like this : `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 `items` as `a` inner join `attr_maps` as `b` on `b`.`number` = `a`.`number` group by `a`.`number` having {$brand} = honda)` – moses toh Sep 09 '18 at 05:54
Okay, thanks. Please see my question. I had update it. Actually the having raw is dynamic – moses toh Sep 09 '18 at 06:12
I'm still confused to implement it if the brand is dynamic – moses toh Sep 09 '18 at 06:14
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')` – moses toh Sep 09 '18 at 07:31
my mistake it should be `$$key` in `havingRaw`. updated answer – rkj Sep 09 '18 at 07:39
You shouldn't execute queries inside loops! – MAZux Sep 09 '18 at 07:43
@rkj There exist error : `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')` – moses toh Sep 09 '18 at 07:43
@SuccessMan did you try `{$$key}` ? – rkj Sep 09 '18 at 07:45
I think you should point to that as a 'problem' that may cause a big unnecessary hit on the server. He must either redesign his code or fetch a single query and transform the returning object into what he needs. – MAZux Sep 09 '18 at 07:50
@rkj Now it works. I modify you code to be like this : `foreach($param as $key => $value) { $query = $query->havingRaw("{$$key} = ?", [$value]); } $query = $query->paginate(10); return $query;` – moses toh Sep 09 '18 at 07:54
@SuccessMan let me convert all the code based on function. give me 5 min – rkj Sep 09 '18 at 07:55
@SuccessMan check it now. it is function based – rkj Sep 09 '18 at 08:02
@rkj If I using functions, the results are not in line with my expectations. If I use your answer that is not function based, it works. Try to see the answer in my question. I have updated it – moses toh Sep 09 '18 at 08:13
@SuccessMan no need to use function, i have reverted the code. Final answer updated. should work now – rkj Sep 09 '18 at 08:14
@rkj Great. That's what I mean. Thanks a lot. Have a nice day :) – moses toh Sep 09 '18 at 08:16
@rkj Maybe you can help again. Look at this : https://stackoverflow.com/questions/52356663/why-having-only-worked-on-fields-that-are-group-by-on-laravel-eloquent – moses toh Sep 16 '18 at 17:46

syam · Answer 2 · 2018-09-09T07:51:39.473

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 \Illuminate\Pagination\LengthAwarePaginator($results,     $totalCount, $take, $page);
return $paginator;

There exist error : `Call to a member function paginate() on array` — moses toh, Sep 09 '18 at 07:38

How can I convert conditional aggregation mysql to laravel query?

2 Answers2

Linked