Searching And Sorting On Calculated Values In Cgridview

I can get this working correctly but searching and sorting are not working in CGriedView




 public function search()

 {


  $criteria=new CDbCriteria;


        $criteria->select = array(

         'SUM(t.quantite) as quantiteVendue',

         'SUM(t.quantite) * SUM(t.pu) as montant'

        );

        

  $criteria->compare('quantiteVendue',$this->quantiteVendue);

  $criteria->compare('montant',$this->montant);


  $criteria->group='t.produit_id';


  return new CActiveDataProvider($this, array(

   'criteria'=>$criteria,

  ));

 }



and the plain SQL is




SELECT SUM(quantite) quantite ,SUM(quantite) * SUM(pu) Montant

FROM `vente` 

GROUP BY `produit_id`




and I know that there is problem with




  $criteria->compare('quantiteVendue',$this->quantiteVendue);

  $criteria->compare('montant',$this->montant);



So how compare with calculated values ?

Dear Friend

I see there is no join involved in your example.

Then kindly check the following.




 public function search()

 {


  $criteria=new CDbCriteria;


        $criteria->select = array(

         'SUM(t.quantite) as quantiteVendue',

         'SUM(t.quantite) * SUM(t.pu) as montant'

        );

        

  $criteria->compare('SUM(t.quantite)',$this->quantiteVendue);

  $criteria->compare('SUM(t.quantite) * SUM(t.pu)',$this->montant);


  $criteria->group='t.produit_id';


  return new CActiveDataProvider($this, array(

   'criteria'=>$criteria,

   'sort'=>array('attributes'=>array(quantiteVendue=>array(),montant=>array())),

  ));

 }



I am sure that you have declared both "quantiteVendue" and "montant" as virtual properties and also made them

safe on search.

Regards.

Of course!

Working fine like that




 public function search()

 {


  $criteria=new CDbCriteria;


        $criteria->select = array(

         'SUM(t.quantite) as quantiteVendue',

         'SUM(t.quantite) * SUM(t.pu) as montant'

        );

        

  $criteria->compare('SUM(t.quantite)',$this->quantiteVendue);

  $criteria->compare('SUM(t.quantite) * SUM(t.pu)',$this->montant);


  $criteria->group='t.produit_id';


  return new CActiveDataProvider($this, array(

   'criteria'=>$criteria,

   'sort'=>array('attributes'=>array('quantiteVendue'=>array(),'montant'=>array())),

  ));

 }



Thanks you my friend (+1 ;))

Hi again!

I just noticed that only sorting is working but not searching

General error:1111 Invaliid use of group function.

Dear Friend

Kindly check after altering the these lines.




$criteria->compare('t.quantite',$this->quantiteVendue);

  $criteria->compare('t.quantite * t.pu',$this->montant);



Yes! Working… THANKS!

Dear Friend

This is just to simulate your scenario.

I got a table wage.

Model:Wage.

Attributes:id,name,days,hours.

virtual attributes:duration,salary.




public function search()

	{

		

		$criteria=new CDbCriteria;

		$criteria->select="t.*,SUM(t.days*t.hours) AS duration,SUM(t.days*t.hours*75) AS salary";

		$criteria->group="t.id";

		$criteria->compare('id',$this->id);

		$criteria->compare('name',$this->name,true);

		$criteria->compare('days',$this->days);

		$criteria->compare('hours',$this->hours);

		$criteria->compare('t.days*t.hours',$this->duration);

		$criteria->compare('t.days*t.hours*75',$this->salary);


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'sort'=>array("attributes"=>array("duration"=>array(),"salary"=>array())),

		));

	}



admin.php




<?php $this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'wage-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'columns'=>array(

		'id',

		'name',

		'days',

		'hours',

		'duration',

		'salary',

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>




This is working well.

In the above scenario we used GROUP BY.

In my case ,there is no need for it as we are dealing with single table.

The following is also working.




public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;

		$criteria->select="t.*,t.days*t.hours AS duration,t.days*t.hours*75 AS salary";

		//$criteria->group="t.id";//without GROUP BY

		$criteria->compare('id',$this->id);

		$criteria->compare('name',$this->name,true);

		$criteria->compare('days',$this->days);

		$criteria->compare('hours',$this->hours);

		$criteria->compare('t.days*t.hours',$this->duration);

		$criteria->compare('t.days*t.hours*75',$this->salary);


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'sort'=>array("attributes"=>array("duration"=>array(),"salary"=>array())),

		));

	}



I perceive that the situation here does not need GROUP BY.

My search function is like this





public function search()

 {


  $criteria=new CDbCriteria;

  		$criteria->with=array('produit');

        $criteria->select = array(

         'SUM(t.quantite) as quantiteVendue',

         'SUM(t.quantite) * SUM(t.pu) as montant'

        );

        

  $criteria->compare('t.quantite',$this->quantiteVendue);

  $criteria->compare('t.quantite * t.pu',$this->montant);


  $criteria->group='t.produit_id';


  return new CActiveDataProvider($this, array(

   'criteria'=>$criteria,

   'sort'=>array('attributes'=>array('quantiteVendue'=>array(),'montant'=>array())),

  ));

 }



And it looks like working but

when a make a test with different values of quantiteVendue and montant I noticed that for some values the gried shows

"No results found." but I can see the value without searching so something is not going fine…

Dear Friend

I am not aware that you are using joining.

Then if it HAS::MANY relation,then GROUP BY is handy.

In that we can use HAVING,

But the limitation here is I can only use one calculated attribute.

I have successfully made a attempt in the following thread.

How To Make Custom Column Of Cgridview Filterable ?

Before I have found out that HAVING is useful, I also had some weird outputs.Sometimes

I was getting values not existing in the paricular row.

I hope that there should be some better solution for this kind of scenarios.

Someone will definitely resolve this.

Regards.

NOTE:Things are easy when we are using CArrayDataProvider.

quantiteVendue |montant|

------------------------|

1 |200 |

4 |4800 |

5 |900 |

18 |3240 |

47 |15510|

77 |69300|

------------------------|

For my test I’m getting 'No result found ’ for the last two values of quantiteVendue and quantiteVendue

Dear Friend

This is getting desperate.

Kindly check the following.

Just do the following:

1.Drop with;use direct join.

2.Drop compare methods for calculated values.




public function search()

{

    $criteria=new CDbCriteria;

    $criteria->join="LEFT JOIN produit ON something=someOtherthing";

    $criteria->select ="t.*,SUM(t.quantite) as quantiteVendue,(SUM(t.quantite) * SUM(t.pu)) AS montant";

    $criteria->group='t.produit_id';


    return new CActiveDataProvider($this, array(

       'criteria'=>$criteria,

       'sort'=>array('attributes'=>array('quantiteVendue'=>array(),'montant'=>array())),

          ));

}



kindly check whether we get expected results on Grid.

After everything is fine, add the following method to the current model.




function addHaving($criteria,$sql,$attribute,$joint=" AND ")

{

	($criteria->having!=="")?$joint=$joint:$joint="";

		

	if(isset($attribute)&&preg_match('/^(>|<|<>|>=|<=|=|){1}(\d)*(\.)?(\d)*$/',$attribute))

        {

		if(is_numeric($attribute))

			$criteria->having.=$joint.$ql."=".$attribute;

		else $criteria->having.=$joint.$sql.$attribute;

	}

	return $criteria;

}



Now add the lines below to the search method before returning dataprovider.




$this->addHaving($criteria,'SUM(t.quantite)',$this->quantiteVendue);

$this->addHaving($criteria,'SUM(t.quantite) * SUM(t.pu)',$this->montant);



Please give your feedback.

cheers.

will try soon… was just little busy this week…

Ohh! Working well this time my friend… I can see that you get a deep undestanding of searching via CgriedView. I’ll post my working code soon

My search function




public function search()

 {


  $criteria=new CDbCriteria;

  		$criteria->select = array(

         'SUM(t.quantite) as quantiteVendue',

         'SUM(t.quantite) * SUM(t.pu) as montant'

        );


$this->addHaving($criteria,'SUM(t.quantite)',$this->quantiteVendue);

$this->addHaving($criteria,'SUM(t.quantite) * SUM(t.pu)',$this->montant);

$criteria->group='t.produit_id';


  return new CActiveDataProvider($this, array(

   'criteria'=>$criteria,

   'sort'=>array('attributes'=>array('quantiteVendue'=>array(),'montant'=>array())),

  ));

 }



and




function addHaving($criteria,$sql,$attribute,$joint=" AND ")

{

        ($criteria->having!=="")?$joint=$joint:$joint="";

                

        if(isset($attribute)&&preg_match('/^(>|<|<>|>=|<=|=|){1}(\d)*(\.)?(\d)*$/',$attribute))

        {

                if(is_numeric($attribute))

                        $criteria->having.=$joint.$sql."=".$attribute;

                else $criteria->having.=$joint.$sql.$attribute;

        }

        return $criteria;

}



and in CgriedView




/*************/

	'columns'=>array(


		'quantiteVendue',

		'montant',

	)

/*************/