CGridView filters with CArrayDataProvider

Hi everyone,

I’m new to Yii and from what i’ve seen, i really like it.

In my application, i decided to use the CGridView to display a list with these columns :




English Word - French Word - German Word - word type - comments



Since my word table doesn’t have a column for each language (fortunately), I need to restructure the data after retrieving it from database. Therefore, I decided to use a CArrayDataProvider, since i can’t use ActiveDirectory.

I’ve succeeded to display the grid with the data, configuring the sorting, but i’m stuck with using the filters.

How i could do it?

Also, if anyone know that i could use ActiveRecord in my case, i’d glad to listen to him.

Thanks.

Query for retrieving data


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

                    ->select('e.id, w.label, l.code as languageCode, t.code as type, g.code as gender, e.comments')

                    ->from('wordentry e')

                    ->join('word w', 'w.wordentry_id = e.id')

                    ->join('languages l', 'w.languages_id = l.id')

                    ->join('wordgender g', 'w.wordgender_id = g.id')

                    ->join('wordtype t', 'e.wordtype_id = t.id')

                    ->where('e.users_id = :id', array(':id' => $this->_params['userId']))

                    ->order('e.id')

                    ->queryAll();

Hi Calvaria,

Welcome to the forum… The forum is full of posts regarding the same problem…

Here just an example: http://www.yiiframework.com/forum/index.php?/topic/10514-cgridview-filter-control/page__p__51640__hl__CGridView#entry51640

I remember reading the forum quite extensively when i first started learning Yii. You will find most of the first arisen questions there for sure.

Again… bienvenido

I’ve already searched about filters in CGridView, but in my case, i can’t use ActiveRecord, since a row represent a multiple instance of a entity (word).

Since CGridView filter attribute need an instance of CModel, I can create a class, like, FilterModel which extends CModel and use it for the CGridView. But, I think that it is dirty, so, if possible, i’d like to find another way.

Hi,

I’m trying to accomplish the same, i.e. I have a CArrayDataProvider for a CGridView, so I need a Model to be able to use filters.

Were you able to make this work ?

If so, could you please show how you did it ?

Thanks

To make it work, i created a model that extends the CFormModel (but i’m sure that you could also extend CModel).

For each columns, you need to create a attribute with the column name, or like me, create a private array with a magic getter :




class FiltersForm extends CFormModel

{

    private $_filters = array();

    public function __get($name)

    {ou 

        return $this->_filters[$name];

    }

}



(array keys match to columns name)

Now you can use your form as a model for filters. Then, you should find the filters value in $_GET.

Thank you so much Calvaria.

[s]I’m not familiar with magic functions, but I’ll give it a try.

[/s]

I had to add one line to the __get function, and it seems to be working now, well, still need to do more tests, but I can see the filters now.




class FiltersForm extends CFormModel

{

    private $_filters = array();

    public function __get($name)

    { 

        $this->_filters[$name] ='';

        return $this->_filters[$name];

    }

}



I was actually tryin to create dynamic class attributes, but I’m getting an error ( I posted this question

Thanks again!

I’m not sure, but you cannot create dynamic class attribute.

If you look at ActiveRecord class, you’ll see that it uses the same code :

  • an array attribute

  • a magic getter

And why would you want to do this?

Hi Calvaria,

I hope you won’t be offended with what I am going to say but I think you have not fully grasped the concept of Yii and MVC. This is not a set of libraries that you use, it is a framework. I suggest that you read “The definitive guide to Yii” and fully understand the concept of MVC, the use of Models, Relations and especially ActiveRecord. I hope you approach this with an open mind as you can not fill a cup that is already full.

To answer your question about ActiveRecord – array attribute and the magic getter, it is made like that because an ActiveRecord represents a row in table. Using the magic getter you can call ActiveRecord->columnName and you will get the data of that attribute of that particular row. Now why an attribute array? Basically, an array best stores multiple attributes with matching column name i.e. array(‘columnName’ => ‘attribute’…)

But, in saying this, I genuinely want to help. If you need to get this solved ASAP, please elaborate your initial question and post table schemas, related controllers and related models.

Thank you very much!

Jaymard

Hi Jaymard,

I thought that I’ve explained it in my first post about my specific problem with CGridView filters. The filter attribute needs a instance of CModel to work and since i can’t use a ActiveRecord instance because of a specific data structure, I decided to create a instance of CModel (I choose CFormModel, but I probably shouldn’t) to make it work.

If my way to solve this problem seems not to be MVC compliant to you then explain me why.

I’ll gladly listen to you explanation.

And I never had any question about ActiveRecord and magic getter. I just wanted to know why yiisus wanted to make dynamic attributes without using magic getter and array attribute (but it seems that it’s not the case anymore)

To yiisus :

Sorry, I forgot to say that you need to instancied any post in the array to make it work.

In my situation, I added a function :




    public function setColumnsToFilters(array $filters)

    {

        foreach($filters as $f)

        {

            $this->_filters[$f] = '';

        }

    }



Of course, the variable $filters is dynamic, it depends on the results of the query above.

Hi Calvaria,

Thank you for taking my comment really well. :) Question, have you actually created models for your tables (Word, Languages, WordGender, WordType, WordEntry) that inherits CActiveRecord?

I think we can only create dynamic attributes through the magic functions.

I wanted to create dynamic attributes because the CArrayDataProvider I’m using will change dynamically, so I can not create a CModel/CFormModel with fixed attributes. But things are working now with the __get function you provided.

Thanks again.

Yes, I generated these models via Gii.

But I’m not sure it’s possible to use only ActiveRecord, since a row in the grid is made of multiple instance of Word.

That’s great! If the relationships are set properly, then you can have them shown in the grid:




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

        'dataProvider' => $dataProvider,

        'filter' => $model,

        'columns'=>array(

                'id',

                array(

                        'name'=>'Word',

                        'value'=>'$data->word->label',

                        'filter'=> CHtml::listData(Word::model()->findAll(), 'id', 'label'),

                ),

        ),

)); ?>



Since a WordEntry has many Word, this cannot work (error during eval()).

And with your code, I’ll only have one word, not a row with many words, depending of the number of languages.

Anyway, I decided to use some jQuery plugin for this situation, since it was more suitable. (jqgrid with inline editing).

helo Calvaria

i’m make a Cgridview from CArrayDataProvider but i can’t filter input…

can you teach me how to filtering it using CFormModel?

I search the forum but no one has the solution yet…

I appreciate your reply

thanks

Hi,

Like I said previously, the ‘filters’ attribute of CGridView must be an instance of CModel. So that’s why I wrote this :


class FiltersForm extends CFormModel

{

    private $_filters = array();

    public function __get($name)

    {

        return $this->_filters[$name];

    }

}

The class FiltersForm extends CFormModel (which extends CModel). Thus, you could use it in the filters attribute of CGridView.

In order to create the filter input, you’ll need to create some getter methods for each column in your CArrayDataProvider (at least, for each column you want to display).

In my case, I used a magic getter. I also initialize every column, but you could also do like :


class FiltersForm extends CFormModel

{

    private $_filters = array();

    public function __get($name)

    {

        if(!array_key_exists($name, $this->_filters))

            $this->_filters[$name] = '';

        return $this->_filters[$name];

    }

}

With this, you should be able to create filter input. After that, you’ll be able to retrieve the filter values in $_POST and use them in your query.

I hope I was clear :) .

oh i see…

but my array data is complex and i also use a stored procedure to retrieve data… i can’t use $_POST value direct to my sql query…

i’ll show you my array data:




Yii::app()->db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);

		$items=ProductDelivery::model()->findAll("category=:param",array(":param"=>"Populasi"));

		$data=array();

		foreach($items as $prodel)

		{

			$itemsdetail=ProductDeliveryDetail::model()->findAll("productDelivery_id=:param",array(":param"=>$prodel["id"]));

			foreach($itemsdetail as $prodeldetail)

			{

					$sql="SELECT count(a.cust_loc_id) as qty,b.site FROM pd_serial_detail a inner join pd_customer_location b on a.cust_loc_id=b.id WHERE a.prodel_detail_id=$prodeldetail[id] GROUP BY a.cust_loc_id;";

					$command1 =Yii::app()->db->createCommand($sql); 

					$res1 =$command1->queryAll();

					$cust=Customer::model()->findByPk($prodel["customer_id"]);

			$qtxt="call search('$prodeldetail[product_number]');";

			$command =Yii::app()->db->createCommand($qtxt);

			

			$res =$command->queryAll();

			Yii::app()->db->setActive(false);

				foreach($res as $parts)

				{

					Yii::app()->db->setActive(true);

					$partstruc=PartStructure::model()->findByAttributes(array("number"=>$parts["number"]));

					

					foreach($res1 as $detail)

						{

							$tot=(int)$detail["qty"]*(int)$parts["qty"];

							$data[]=array(

							"PartNumber"=>$parts["number"],

							"Description"=>$parts["description"],

							"Application"=>$prodeldetail["product_desc"],

							"Population"=>$detail["qty"],

							"Quantity"=>$parts["qty"],

							"TotalParts"=>$tot,

							"TotalPrice"=>(int)$partstruc->part->price*(int)$tot,

							"Location"=>$detail["site"],

							"Customer"=>$cust->nama

								);

						}

				}

			}

		}



as you see my $data array is complex

i think i’ll get the $_POST and do some strpos() loop (maybe i will just give filter to 1 column) and save new array data that match and give it to the CGridView…

hey anyway, thanks bro for your reply

nb: sorry for my bad english

Since there was no clearly documented solution yet, I took your idea into a wiki page:

http://www.yiiframework.com/wiki/232/using-filters-with-cgridview-and-carraydataprovider/

I saw this thread seems like almost similar with my problem,

But Sometimes I decide to use Command Builder (CdbCommand) to Build a log query, Can I trick CGridView to use the CommandBuilder result? Or any kind of Solution to show the data come from CommandBuilder to shown similarly as CGridView ?

Thanx