searching and sorting on calculated values in CGridView

i have a problem figuring out how to perform searches and how to sort the CGridView on calculated values.

i have an invoice model with a sum to pay. for every invoice there can be several payments which should add up to that sum in order to know if the invoice may be considered as ‘paid’.

in my invoice model i have a relation:

public function relations()

	{

		return array(

                    'payments'      => array(self::HAS_MANY, 'Payment', 'invoice_id'),

                    'paymentsSum'   => array(self::STAT,     'Payment', 'invoice_id',

                        'select'     => 'SUM(amount)',),

		);

	}

and a function for determining if it’s paid:

public function isPaid()

        {

            if($this->amount - $this->paymentsSum <= 0)

                return TRUE;

            else

                return FALSE;

        }

i can use it to show the ‘paid’ status on CGridView, but it’s not sortable.

furthermore i cannot use it for searches to show only paid, or unpaid invoices.

what should I do? adding:

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

to the search criterias apparently doesn’t work

In your CGridView are you displaying if the products is paid or not, or are you displaying the sum remaining to pay? (just trying to understand what you are trying to do so i could help you better!)

If this is display if the products is paid or not I would display a dropdown list in my CGridView containing 1=>"Paid" and 0=>"Not Paid" named paidProducts and in the criteria i would do something like

if($this->paidProducts){

$criteria->condition = '(t.amount - SUM(Payment.amount))<=0';

} else {

$criteria->condition = '(t.amount - SUM(Payment.amount))>0';

}

Edit: I made a mistake, you were talking about sorting and i answered you about filtering ^^

sorting can only be applied on SQL level. You must move all calculations you want to sort on to the database.

to allow sorting on virtual attribute ‘isPaid’ you have to:

1. add getter in your model:

public function getIsPaid()  //important is to add 'get' prefix to allow the magic work <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/smile.gif' class='bbc_emoticon' alt=':)' />

        {

            if($this->amount - $this->paymentsSum <= 0)

                return TRUE;

            else

                return FALSE;

        }

2. add sorting definition in your dataProvider (by default returned from search function):

return new CActiveDataProvider( 'Payment', array(

    'criteria'=>$criteria,

    'sort'=>array(

        'attributes'=>array(

            'isPaid'=>array(

                'asc'=>'( t.amount - t.paymentsSum )',  //where t is default alias for primary table in query

                'desc'=>'( t.amount - t.paymentsSum ) DESC',

            ),

            '*', //leave this or you won't be able to sort by default attributes

        ),

    ),

));

3. use this attribute in grid:

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

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

    'filter'=>$model,

    'columns'=>array(

        'amount',

        'isPaid',

    ),

));

Above is a little bit tricky because displayed value will be get from getIsPaid getter, but Yii will sort on SQL condition, not the php value. This way results can be a little bit different but you can tune this

also read this: http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview/

Hi wuadziu,

STAT relation is always lazy-loaded.

So you can not use ‘paymentsSum’ (and ‘isPaid’ that relies on ‘paymentsSum’) for filtering and sorting.

You have to use a sub query to get ‘paymentsSum’ and ‘isPaid’ in your search() method.

The following wiki is for a ‘COUNT’ STAT relation, but I think you can modify it for your ‘SUM’ STAT relation.

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview

thank you all for your help. i finally managed to do it using softarks link.

the most of what i’ve done is identical to the wiki tutorial, so i’ll just show the code from the search() function.

// subquery to retrieve the 'paid' status

                $payment_table   = Payment::model()->tableName();

                

                $paid_status_sql = "(select CASE WHEN t.amount-(select sum(amount) from $payment_table where $payment_table.invoice_id = t.id) <= 0 THEN 1 ELSE 0 END)";

                

                // select

                $criteria->select = array(

                    '*',

                    $paid_status_sql."as isPaid",

                );

                

                // where

                $criteria->compare($paid_status_sql, $this->isPaid);