[SOLVED] Queries, DataProvider, Counters and Statistics

Yii 2.0 General Discussions
1

I need to show data from a search and display some statistics on a Google chart.

i have this structure:





Model: "Results" and "ResultsSearch"

Controller: "ResultsController"

Action: "actionConsulta"

View: "consulta" and the search form "_buscador"

The query returns the list of "Results" and the view show it in a GridView with the following fields:

Code - Type - Date - Status.

Additionally I need to show the total statistics of the "Results" with Status=1 (success) and with Status=0 (Failure) and also need to show statistics grouped by months, ie:




Example 1: Search "Results" of "Type = A" from "Start Date = January 1" to "End Date = March 1"

Total: 12 Success - 10 Failure


January: 1 Success - 5 Failure

February: 3 Success - 1 Failure

March 8 Success - 4 Failure





Example 2: Search for "Results" of "Type = A" from "Start Date = February 20" to "End Date= March 15"

Total: 15 Success - 10 Failure


February: 3 Success - 1 Failure

March 12 Success - 9 Failure


etc....

I have implemented with a global search to display data in the GridView:


Example 1

Code - Type - Date - Status.

01 - A - 01-01-2015 - Success

02 - A - 02-01-2015 - Failure

03 - A - 03-01-2015 - Failure

04 - A - 04-01-2015 - Failure

05 - A - 05-01-2015 - Failure

06 - A - 06-01-2015 - Failure

07 - A - 01-02-2015 - Success

08 - A - 02-02-2015 - Success

09 - A - 02-02-2015 - Success

10 - A - 03-02-2015 - Failure


etc....

To show the total stats I have made the same query for each Status (Status Success and [size="2"]Status[/size][size="2"] Failure) to then use ->[/size]getTotalCount, As the query is the same i use a function for the global query and another for the global DataProvider and then i use $query->andFilterWhere with the status param.





class ResultsSearch extends Results

{


    public function consultaQuery($params)

    {

            $query = Resultados::find();

            $this->load($params);

            if (!$this->validate()) {

                $query->where('0=1');

                return $query;

            }

            

            $query->andFilterWhere([

                'type' => $this->type,

                'date' => $this->date,

            ]);


            $query->andFilterWhere(['between', 'results.date', $this->dateStart, $this->dateEnd]);

            return $query;

    }

    public function consultaDataProvider($query)

    {

            $dataProvider = new ActiveDataProvider([

                'query' => $query,

            ]);

            $dataProvider->setSort([

                'attributes' => [

                    'id',

                    'type',

                    'date',

                    'status',

                ]

            ]);

            return $dataProvider;

    }

    public function consulta($params)

    {

            $query = $this->consultaQuery($params);

            $dataProvider = $this->consultaDataProvider($query);

            

            $queryAptos = $this->consultaQuery($params);

            $queryAptos->andFilterWhere(['status' => 1]);

            $dataProviderAptos = $this->consultaDataProvider($queryAptos);

            

            $queryNoAptos = $this->consultaQuery($params);

            $queryNoAptos->andFilterWhere(['status' => 0]);

            $dataProviderNoAptos = $this->consultaDataProvider($queryNoAptos);

            

            $dataResult['dataProvider']=$dataProvider;

            $dataResult['aptos']=$dataProviderAptos->getTotalCount();

            $dataResult['noAptos']=$dataProviderNoAptos->getTotalCount();

            

            return $dataResult;

    }

}

To show the statistics by month I try to made a query for each Status (Status Success and [size="2"]Status[/size][size="2"] Failure) and for each month:[/size]

[size="2"]





    public function consulta($params)

    {

            $query = $this->consultaQuery($params);

            $dataProvider = $this->consultaDataProvider($query);

            

            $queryAptos = $this->consultaQuery($params);

            $queryAptos->andFilterWhere(['status' => 1]);

            $dataProviderAptos = $this->consultaDataProvider($queryAptos);

            

            $queryNoAptos = $this->consultaQuery($params);

            $queryNoAptos->andFilterWhere(['status' => 0]);

            $dataProviderNoAptos = $this->consultaDataProvider($queryNoAptos);

            

            $dataResult['dataProvider']=$dataProvider;

            $dataResult['aptos']=$dataProviderAptos->getTotalCount();

            $dataResult['noAptos']=$dataProviderNoAptos->getTotalCount();

            

            $queryAptosEne = $this->consultaQuery($params);

            $queryAptosEne->andFilterWhere(['status' => 1]);

            $queryAptosEne->andFilterWhere(['between', 'results.date, date("2015-01-01"), date("2015-01-31")]);

            $dataProviderAptosEne = $this->consultaDataProvider($queryAptos);

            

            $queryNoAptosEne = $this->consultaQuery($params);

            $queryNoAptosEne->andFilterWhere(['status' => 0]);

            $queryNoAptosEne->andFilterWhere(['between', 'results.date', date("2015-01-01"), date("2015-01-31")]);

            $dataProviderNoAptosEne = $this->consultaDataProvider($queryNoAptos);

            

            $ene['aptos']=$dataProviderAptosEne->getTotalCount();

            $ene['noAptos']=$dataProviderNoAptosEne->getTotalCount();

            

            $months['ene']=$ene;

            

            $dataResult['months']=$months;

            

            return $dataResult;

    }

[/size]

This is my view "consulta"





<div id="results-lista">

	//Show total stats

    <?=Html::encode('Total: '$results['dataProvider']->getTotalCount().' - Aptos: '.$results['aptos'].' - No Aptos: '.$results['noAptos'])?>

	//Show January stats

    <?=Html::encode('Enero - Aptos: '.$results['month']['ene']['aptos'].' - No Aptos: '.$results['month']['ene']['noAptos'])?>

	//Search Form

    <?php echo $this->render('_buscador', ['model' => $resultsSearchModel]); ?>

	//GridView

        <?=  GridView::widget([

            'dataProvider' => $resultsDataProvider,

			'filterModel' => $resultsSearchModel,

			'columns' => [

                    'id',

					'type',

					'date',

					'status',

			],

		]);

	?>

</div>

[size="2"]I have no idea if this is the way to do it, or if there are some other ways.[/size]

With the following query in MySQL I get statistical data for months but i don’t know how to include it in the query and then how to use the stats on the view:





select code, type, date, status,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-01-01 00:00:00' AND '2015-01-31 00:00:00') then 1 else 0 end) AptoEne,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-02-01 00:00:00' AND '2015-02-29 00:00:00') then 1 else 0 end) AptoFeb,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-03-01 00:00:00' AND '2015-03-31 00:00:00') then 1 else 0 end) AptoMar,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-04-01 00:00:00' AND '2015-04-30 00:00:00') then 1 else 0 end) AptoAbr,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-05-01 00:00:00' AND '2015-05-31 00:00:00') then 1 else 0 end) AptoMay,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-06-01 00:00:00' AND '2015-06-30 00:00:00') then 1 else 0 end) AptoJun,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-07-01 00:00:00' AND '2015-07-31 00:00:00') then 1 else 0 end) AptoJul,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-08-01 00:00:00' AND '2015-08-31 00:00:00') then 1 else 0 end) AptoAgo,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-09-01 00:00:00' AND '2015-09-30 00:00:00') then 1 else 0 end) AptoSep,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-10-01 00:00:00' AND '2015-10-31 00:00:00') then 1 else 0 end) AptoOct,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-11-01 00:00:00' AND '2015-11-30 00:00:00') then 1 else 0 end) AptoNov,

    sum(case when (status = '1' AND orders.date_order BETWEEN '2015-12-01 00:00:00' AND '2015-12-31 00:00:00') then 1 else 0 end) AptoDic,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-01-01 00:00:00' AND '2015-01-31 00:00:00') then 1 else 0 end) NoAptoEne,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-02-01 00:00:00' AND '2015-02-29 00:00:00') then 1 else 0 end) NoAptoFeb,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-03-01 00:00:00' AND '2015-03-31 00:00:00') then 1 else 0 end) NoAptoMar,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-04-01 00:00:00' AND '2015-04-30 00:00:00') then 1 else 0 end) NoAptoAbr,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-05-01 00:00:00' AND '2015-05-31 00:00:00') then 1 else 0 end) NoAptoMay,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-06-01 00:00:00' AND '2015-06-30 00:00:00') then 1 else 0 end) NoAptoJun,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-07-01 00:00:00' AND '2015-07-31 00:00:00') then 1 else 0 end) NoAptoJul,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-08-01 00:00:00' AND '2015-08-31 00:00:00') then 1 else 0 end) NoAptoAgo,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-09-01 00:00:00' AND '2015-09-30 00:00:00') then 1 else 0 end) NoAptoSep,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-10-01 00:00:00' AND '2015-10-31 00:00:00') then 1 else 0 end) NoAptoOct,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-11-01 00:00:00' AND '2015-11-30 00:00:00') then 1 else 0 end) NoAptoNov,

    sum(case when (status = '0' AND orders.date_order BETWEEN '2015-12-01 00:00:00' AND '2015-12-31 00:00:00') then 1 else 0 end) NoAptoDic,

    count(*) total,

    sum(case when status = '1' then 1 else 0 end) Apto,

    sum(case when status = '0' then 1 else 0 end) NoApto

from results

where results.cliente=28

I have also thought about the possibility of a only one global query and then filter data based on dates.

2

I would rather loop through the result set of the main query (or, global query in your term) in PHP layer to gather the stats.

3

Hi softark, Thanks!!

Could you give a little sample?

The loop is over the dataProvider? Maybe using $dataProvider->getModels()?

i´m loose…

Thanks again!!

4

Well, something like this:




$total = [];

$monthTotal = [];

foreach ($dataProvider->getModels() as $model) {

    $status = $model->status;

    if (isset($total[$status])) {

        $total[$status]++;

    } else {

        $total[$status] = 1;

    }

    $month = $model->getMonth();  // get the month part of the date

    if (isset($total[$month][$status])) {

        $monthTotal[$month][$status]++;

    } else {

        $monthTotal[$month][$status] = 1;

    }

}

I’m not sure if it’s faster than sql way of doing, but I prefer the easier way. :P

[EDIT]

I would do it in the same way as yours, if I don’t need to collect the monthly stats.

5

Well, i think you are right, maybe SQL is faster, but as i don’t know how to do it i choose the easy way too.

Thank you very much [size=2]softark!!![/size]

[size=2]SOLVED!!![/size]