Show query result in clistview and cgridview

Yii 1.1 General Discussion
1

Hi guys,

I wan to show the query result in cgridlist and clistview?? how to show in view?? any suggestion?


        $user = Yii::app()->db->createCommand()

                ->select('u.email,u.id,u.mobile_phone_number,c.company_name')

                ->from('user u')

                ->join('company c', 'u.id=c.user_id')

                ->where('u.email=:email', array(':email' => $userId))

                ->queryAll();

thank, =)

2

Hi!

Yii works with ORM… use it!. gridview and listview needs dataprovider. so if u wanna use sql then read CSqlDataProvider.

http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider

3

Hi Wartex,

how to get the result In CGridView from other table? i wan show user and company info in 1 cgridview.


 <?php

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

        'id' => 'detail-grid',

        'dataProvider' => $dataProvider,

        'filter' => $dataProvider,

        'columns' => array(

            'id',

            'user_name',

            'email',

            'company_name', //get comp_name at company table

id,user_name,email is from user table and company_name from company table.

4

You can use relational AR in grid/listviews just like doing a plain AR query.

In order for this example to work you need to set relations. See in guide:

http://www.yiiframework.com/doc/guide/1.1/en/database.arr

Since you have 2 models here, User and Company…

In User model, set company relation (named companies). User BELONGS_TO Company


  'companies'=>array(self::BELONGS_TO, 'Company', 'user_id')

In Company model, set user relation (named users). Company HAS_MANY User.


'users'=>array(self::HAS_MANY, 'User', 'user_id')

Then you can do this:




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

        'id' => 'detail-grid',

        'dataProvider' => $dataProvider,

        'filter' => $dataProvider,

        'columns' => array(

            'id',

            'user_name',

            'email',

            //'company_name', get comp_name at company table

            'companies.company_name' //uses relation in model to query related data
5

Hi dniznick,

How to write the code in controller view? As below? plus if i wan filter the companies.company name, how to write in search() condition?


     

Controller:

   $dataProvider = new CActiveDataProvider('company');

        $this->render('company_view', array(

            'dataProvider' => $dataProvider,



    public function search()

    {

      

        $criteria = new CDbCriteria;


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

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

        $criteria->compare('company_name', $this->company-name, true);   //get the value from companies.company_name

 

        $criteria->with = array('company');

        $criteria->together = true;




        return new CActiveDataProvider(get_class($this), array(

            'criteria' => $criteria,

        ));

    }

Thanks, anyone can give me ideas… =)

6

Check this (Zaccaria’s post):

http://www.yiiframework.com/forum/index.php?/topic/16095-cgridview-filtering-related-tables/