Yii Framework Forum: [Solved] Wiki : Searching And Sorting By Count Of Related Items In Cgridview - Yii Framework Forum

Jump to content

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

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

#1 User is offline   Daniel 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 226
  • Joined: 26-September 09

Posted 01 March 2013 - 05:52 PM

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),
            ),
        ));
    }

0

#2 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,084
  • Joined: 16-February 11
  • Location:Japan

Posted 02 March 2013 - 12:01 AM

Hi Daniel,

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

The following is the comment on the wiki:

Quote

This tutorial is really helpful, but I got issue with 0 value, if the field result for example $post_count = 0. Then I cannot filter to display it. For example, I have two rows, one has post_count = 0 and the other one has = 5. If I filter "> 3". it will show row which value is 5, but no post_count display for 0. Even, if I filter with "= 0", = "0.0" and "< 2.0".


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

#3 User is offline   Reza m 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 148
  • Joined: 07-March 12

Posted 02 March 2013 - 02:07 AM

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)";


0

#4 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,084
  • Joined: 16-February 11
  • Location:Japan

Posted 02 March 2013 - 02:27 AM

@Reza m

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

#5 User is offline   Daniel 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 226
  • Joined: 26-September 09

Posted 03 March 2013 - 08:19 AM

@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",
        );

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