Yii Framework Forum: Custom Model For Cobination Of Multiple Tables - Yii Framework Forum

Jump to content

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

Custom Model For Cobination Of Multiple Tables Rate Topic: -----

#1 User is offline   martijnjonkers 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 43
  • Joined: 07-June 13

Posted 23 October 2013 - 08:58 AM

hi,

I created the following model
It actually uses other column names etc, this is pure for illustration.

class TestData extends CModel
{
    /* the container to keep the attributes */
    private $__attributes = array();

    /**
    * construct the model
    *
    * @return TestData
    */
    public function __construct( )
    {
        //do whatever you need to do.
    }

    /**
    * The available attributes
    *
    * @return Array the attributes
    */
    public function attributeNames()
    {
        return array(
            'Time',
            'ColumnA',
            'ColumnB',
        );
    }

    /**
    * The attribute labels
    *
    */
    public function attributeLabels()
    {
        return array(
            'Time'    =>'Time',
            'ColumnA' =>'Column in table A',
            'ColumnB' =>'Column in table B',
        );
    }

    /**
     * The search function
     * @return CSqlDataProvider
     */
    public function search()
    {
        $criteria=new CDbCriteria;

        //setup the search variables
        $criteria->compare('__t.Time',$this->Time,true);
        $criteria->compare('ColumnA',$this->ColumnA,true);
        $criteria->compare('ColumnB',$this->ColumnB,true);

        //setup sorting
        $sort = new CSort();
        $sort->defaultOrder = 'Time DESC';
        $sort->attributes = $this->attributeNames();

        //creatae the command
        $command->select = '
            __t.Time AS Time, 
            ColumnA, 
            ColumnB
        ';
        $command->from = '(
            SELECT __a.Time FROM TableA __a UNION
            SELECT __b.Time FROM TableB __b
        ) as __t';
        $command->join = '
            LEFT JOIN TableA ON TableA.Time = __t.Time
            LEFT JOIN TableB ON TableB.Time = __t.Time
        ';
        $command->where = $criteria->condition;
        $command->params = $criteria->params;

        //count the total number of rows
        $countSql = 'SELECT COUNT(*) FROM ('.$command->text.') AS __c';
        $count = Yii::app()->db->createCommand($countSql)->queryScalar($command->params);

        //create the dataprovider
        return new CSqlDataProvider($command,array(
            'keyField'=>'Time',
            'totalItemCount'=>$count,
            'sort'=>$sort,
            'pagination'=> array(
                'pageSize'=>5,
            )
        ));
    }

    /**
    * set attributes
    *
    * @param mixed $name
    * @param mixed $value
    */
    public function __set($name,$data)
    {
        //are the attributes being set?
        if( $name == 'attributes' )
        {
            //only use when data is array
            if(is_array($data))
            {
                //check all values in array
                foreach( $data as $name => $value )
                {
                    //is the requested name a valid attribute?
                    if( in_array( $name , $this->attributeNames() , true ) )
                    {
                        //set the attribute
                        $this->$name = $value;
                    }
                }

                //done
                return;
            }
        }
        else if( in_array( $name , $this->attributeNames() , true ) )
            $this->__attributes[$name] = $data;
        else
            parent::__set($name ,$data);
    }

    /**
    * get attributes
    *
    * @param mixed $name
    */
    public function __get($name)
    {
        //get attributes
        if( in_array( $name , $this->attributeNames() , true ) )
            return isset( $this->__attributes[$name] ) ? $this->__attributes[$name] : null;
        parent::__get($name);
    }
}


With this model i can create a dataprovider with any column i like from every table with a time column.
And with this data i can fill a gridview which is sortable and filterable for all columns.

It works great!!

But i was wondering, is this a normal way of doing this?
Or is there a way simpler method?
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