[Solved] Wiki : Searching And Sorting By Count Of Related Items In Cgridview


(Adinugro) #1

Hi all,

I got an issue with filtering totalCharges = 0. can anyone help? especially softark? many thanks in advance.

Daniel

This is in my model:




<?php


/**

 * This is the model class for table "sales".

 *

 * The followings are the available columns in table 'sales':

 * @property integer $id

 * @property string $salesNo

 * @property string $date

 * @property string $poNo

 * @property integer $customerFk

 * @property integer $salesmanFk

 * @property string $dueDate

 * @property string $collectionGroup

 * @property string $deliveryDate

 * @property string $deliveredBy

 * @property string $receivedBy

 * @property string $operator

 * @property string $memo

 * @property integer $status

 *

 * The followings are the available model relations:

 * @property Customer $customerFk0

 * @property Salesman $salesmanFk0

 * @property SalesDetail[] $salesDetails

 */

class Sales extends MyActiveRecord {


     .....

    public $totalCharges;




    /**

     * @return array validation rules for model attributes.

     */

    public function rules() {

        // NOTE: you should only define rules for those attributes that

        // will receive user inputs.

        return array(

            array('salesNo, date, customerFk, salesmanFk, dueDate, operator, status', 'required', 'message' => '{attribute} wajib diisi!'),

            array('salesNo', 'unique', 'message' => '{attribute} sudah terdaftar!'),

            array('date', 'date', 'format' => array('dd-MM-yyyy', 'yyyy-MM-dd'), 'message' => '{attribute} tidak sesuai format yang ditentukan!'),

            array('customerFk', 'exist', 'allowEmpty' => false, 'className' => 'Customer', 'attributeName' => 'id', 'message' => '{attribute} tidak terdaftar!'),

            array('salesmanFk', 'exist', 'allowEmpty' => false, 'className' => 'Salesman', 'attributeName' => 'id', 'message' => '{attribute} tidak terdaftar!'),

            array('status', 'in', 'allowEmpty' => false, 'range' => array(self::INACTIVE, self::ACTIVE), 'message' => '{attribute} di luar yang telah ditentukan!'),

            array('salesNo, poNo, collectionGroup, deliveredBy, receivedBy, operator', 'length', 'max' => 25, 'message' => '{attribute} maksimal 25 karakter!'),

            array('deliveryDate, memo, newMemo', 'safe'),

            // The following rule is used by search().

            // Please remove those attributes that should not be searched.

            array('customerName, salesmanName, salesNo, date, poNo, customerFk, salesmanFk, dueDate, collectionGroup, deliveryDate, deliveredBy, receivedBy, operator, memo, status, totalCharges', 'safe', 'on' => 'search'),

        );

    }


    /**

     * @return array customized attribute labels (name=>label)

     */

    public function attributeLabels() {

        return array(

            'id' => 'ID',

            'salesNo' => 'No nota',

            'date' => 'Tanggal',

            ...

            'totalCharges' => 'Tagihan',

        );

    }


    /**

     * Retrieves a list of models based on the current search/filter conditions.

     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

     */

    public function search() {

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

        // should not be searched.


        $criteria = new CDbCriteria;

        $criteria->with = array('customer', 'salesman');


        // sub query to retrieve the totalCharges

        $detailTable = SalesDetail::model()->tableName();

        $detailTableSql = "(SELECT SUM((((quantity * unitPrice) * (1 - discOne)) * (1 - discTwo)) * (1 - discThree)) FROM $detailTable WHERE $detailTable.salesFk = t.id)";


        // select

        $criteria->select = array(

            '*',

            $detailTableSql . " as totalCharges",

        );


        $criteria->compare($detailTableSql, $this->totalCharges);


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

        ...


        $sort = new CSort;

        $sort->defaultOrder = 'salesNo DESC';

        $sort->attributes = array(

            'salesNo' => 'salesNo',

            ....

            'totalCharges' => array(

               'asc' => 'totalCharges ASC',

               'desc' => 'totalCharges DESC',

        );


        $sort->applyOrder($criteria);


        return new CActiveDataProvider($this, array(

            'criteria' => $criteria,

            'sort' => $sort,

            'pagination' => array(

                'pageSize' => Yii::app()->user->getState('Sales_pageSize', Yii::app()->params['defaultPageSize']),

                'currentPage' => Yii::app()->user->getState('Sales_page', 0),

            ),

        ));

    }




SEO-friendly links with ActiveRecord?
(Softark) #2

Hi Daniel,

Hmm, I see nothing wrong in your code so far …

The following is the comment on the wiki:

So, the calculation of ‘totalCharges’ is working fine. Am I right?

Then the $detailTableSql is OK.

The problem occurs in "$criteria->compare($detailTableSql, $this->totalCharges)".

Would you please trace the value of $this->totalCharges when you want to filter by it?

Is it really “> 3”, or something that you don’t expect?


(Reza Mms) #3

I think you can solve this problem by using ifnull function like following:




$detailTableSql = "(SELECT IFNULL(SUM((((quantity * unitPrice) * (1 - discOne)) * (1 - discTwo)) * (1 - discThree)),0) FROM $detailTable WHERE $detailTable.salesFk = t.id)";





(Softark) #4

@Reza m

Ah, I see. I didn’t think of it, but SUM() in the query could return NULL.


(Adinugro) #5

@Reza m, @softark,

Thank you for your support. I was thinking of using IFNULL, but implemented in the wrong way like below,

Once again. thanks for your help.




        // sub query to retrieve the totalCharges

        $detailTable = SalesDetail::model()->tableName();

        $detailTableSql = "(SELECT SUM((((quantity * unitPrice) * (1 - discOne)) * (1 - discTwo)) * (1 - discThree)) FROM $detailTable WHERE $detailTable.salesFk = t.id)";


        // select

        $criteria->select = array(

            '*',

            'IFNULL(' . $detailTableSql . ', 0 )' . " as totalCharges",

        );