CODEWITHSUNDEEP
phpmysqlgridviewyii2yii2-advanced-app
Ghassan Awada
Ghassan Awada

Reputation: 11

yii2: how to calculate the sum of a groupby query

i am trying to calculate the sum of a groupby query and send that value to the view in yii2. my current code gets and displays the correct grouping but the sum is not working.

here is the controller code:

public function actionIndex()
    {   
        if( Yii::$app->request->post('search') )
        {
            $from = Yii::$app->request->post('from');
            $to   = Yii::$app->request->post('to');

            switch( Yii::$app->request->post('activity') )
            {
                case 'bills':
                    $searchModel = new Bill();
                    $query = $searchModel::find();
                    $query->where(['BETWEEN', 'teis_bill_purchase_date', $from, $to]);

                    // The problem is in the below sum
                    $query->joinWith('inventory');
                    $query->groupBy('teis_inventory_id');
                    $query->sum('teis_bill_override_cbm');
                    $query->all();

                    $dataProvider = new ActiveDataProvider([
                            'query' => $query,
                    ]);

                    return $this->render('index', [
                        'searchModel' => $searchModel,
                        'dataProvider' => $dataProvider,
                        'show_results' => 1
                    ]);
                    break;
            }
        }

        return $this->render('index');
    }

i have different types of inventory, and each inventory has multiple bills. i am trying to create a report the gets the number of bills between a specific date range ($from, $to), groups them by the inventory type, and sum up the values of each type and display those values for that type.

here is my view code:

if( isset($show_results) )
        {
            print(GridView::widget([
                'dataProvider' => $dataProvider,
                'filterModel' => $searchModel,
                'columns' => [
                    ['class' => 'yii\grid\SerialColumn'],
                    [
                        'attribute'=>'teis_bill_id',
                        'value'=>'teis_bill_id',
                    ],
                    'inventory.teis_inventory_type',
                    'teis_bill_pieces',
                    'teis_bill_override_cbm',
                    'teis_bill_sale_price',
                    'teis_bill_profit',

                    ['class' => 'yii\grid\ActionColumn'],
                ],
            ]));
        }

i have included the code for the view since i dont know how to display that sum once I get it!

any help would be appreciated. thank you!

Upvotes: 0

Views: 2424

Answers (2)

mrateb
mrateb

Reputation: 2499

The problem here is that $query->all() returns the result as an array, but the result is not saved in any variable.

Try $var = $query->all(); instead, for the result of the executed statement to be saved.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

I think the problem is related to the aliasing of the sum('teis_bill_override_cbm') fields

try select your fields and for the sum use sum('teis_bill_override_cbm') as teis_bill_override_cbm 

$searchModel = new Bill();
$query = $searchModel::find();
$query->select('teis_bill_id, inventory.teis_inventory_type,
                 sum(teis_bill_override_cbm ) as teis_bill_override_cbm, 
                      teis_bill_pieces, teis_bill_sale_price, teis_bill_profit');
$query->where(['BETWEEN', 'teis_bill_purchase_date', $from, $to]);

// The problem is in the below sum
$query->joinWith('inventory');
$query->groupBy('teis_inventory_id');
//$query->sum('teis_bill_override_cbm'); already calculated
$query->all();

Upvotes: 1

Related Questions