Yii Framework Forum: Searching And Sorting On Calculated Values In Cgridview - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Searching And Sorting On Calculated Values In Cgridview I could find an easy way to do it Rate Topic: -----

#1 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 392
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 26 January 2013 - 03:57 PM

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 ?
Posted Image
0

#2 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 27 January 2013 - 09:52 AM

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.
1

#3 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 392
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 27 January 2013 - 10:38 AM

Quote

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

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 ;))
Posted Image
0

#4 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 392
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 27 January 2013 - 10:44 AM

View Postfouss, on 27 January 2013 - 10:38 AM, said:

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
Posted Image
0

#5 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 392
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 27 January 2013 - 10:48 AM

View Postfouss, on 27 January 2013 - 10:44 AM, said:

Hi again!

I just noticed that only sorting is working but not searching


General error:1111 Invaliid use of group function.
Posted Image
0

#6 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 27 January 2013 - 11:21 AM

Dear Friend

Kindly check after altering the these lines.
$criteria->compare('t.quantite',$this->quantiteVendue);
  $criteria->compare('t.quantite * t.pu',$this->montant);

1

#7 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 392
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 27 January 2013 - 11:24 AM

View Postseenivasan, on 27 January 2013 - 11:21 AM, said:

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!
Posted Image
0

#8 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 27 January 2013 - 11:26 AM

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.
0

#9 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 392
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 27 January 2013 - 12:39 PM

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....
Posted Image
0

#10 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 27 January 2013 - 12:56 PM

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.
0

#11 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 392
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 27 January 2013 - 01:20 PM

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
Posted Image
0

#12 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 28 January 2013 - 07:14 AM

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.
1

#13 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 392
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 01 February 2013 - 07:04 PM

will try soon... was just little busy this week...
Posted Image
0

#14 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 392
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 01 February 2013 - 07:25 PM

View Postseenivasan, on 28 January 2013 - 07:14 AM, said:

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.

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
Posted Image
0

#15 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 392
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 01 February 2013 - 07:29 PM

View Postfouss, on 01 February 2013 - 07:25 PM, said:

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',
	)
/*************/

Posted Image
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users