filter by concatenation of model fields in CGridView

Yii 1.1 General Discussion
1

I have a model that includes three columns that hold a version number, in the form of major.minor.patch

I have a CGridView that displays this table and various actions that can be made on it.

I want to display the version number in a single column in the format major.minor.patch, however I also want to be able to filter by that column.

I found this post which points in the right direction . Using that, I have a




public $version;

In the model and




array(

'name'=>'version',

'value'=>'$data->version',

)

In the CGridView definition.

But I don’t know how to populate $model->version from my CActiveDataProvider definition. I can’t specify which columns to select, so I can’t add in a CONCAT for the three columns - or even if this is the right way to go about it.

2

Thinking about it, that will not work, because the data provider is not going to know how to break up the version to apply the filter. Not sure how to go about this.

3

Have you been able to find a solution?

I have a similar situation. I like to display a users full name in the grid, but store them separate in the database (firstName, lastName).

How would I go about setting up the compare for concatenated fields?

4

I am interested in that too!

I tried changing select of my search function:


$criteria->select=array(

  "*", 

  "CONCAT(first_name, ' ', last_name) AS fullName"

);

  ...

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

but it keeps giving me error (yes, I have added public $fullName to my model):


CDbCommand failed to execute the SQL statement: 

SQLSTATE[42S22]: Column not found: 

1054 Unknown column 'fullName' in 'where clause' 

(D:\xampp\htdocs\yii\framework\db\CDbCommand.php:471)
5

Ok, I’ve managed to make it sort by computed column, by adding this to the model search function:


$dataProvider->sort->attributes = array(

  '*', 

  'name'=>array(

    'asc'=>"CONCAT(first_name, ' ', last_name)", 

    'desc'=>"CONCAT(first_name, ' ', last_name) DESC")

  );

But I still can’t make it filter…

6

I’m not sure about the search() but to just get the version number while using the above:

In Model: add




public function getVersion()

{

   return $this->major.'.'.$this->minor.'.'.$this->patch;

}

this will return something like "1.2.3"

Then i would add ‘version’ to the getAttributeLabels() function in the model to set the Header/Label. Just a thought. :blink:

7

petko,

Thanks for the work and update!

I have found a way to make the filtering work.

In the user model I added a new variable, and used this variable in a new search function:




	public $full_name;

...

	public function userSearch()

	{

		$criteria = new CDbCriteria;

		$criteria->compare('user_first_name', $this->full_name, true, 'OR');

		$criteria->compare('user_last_name', $this->full_name, true, 'OR');

		 -- additional criteria --

		$criteria->order = 'user_last_name, user_first_name';

		return new CActiveDataProvider('user', array('criteria' => $criteria, 'pagination' => array('pageSize' => 25)));

	}

I also added the full_name variable to the safe rule.

in the view page:




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

			'id'=>'people-grid',

			'dataProvider'=>$model->userSearch(),

			'filter'=>$model,

			'columns'=>array(

				array(

					'name' => 'full_name',

					'value' => '$data->getFullNameLF()',

				),

				--- other columns ---

			),

		)); ?>

getFullNameLF() returns the full name in last, first format.

This works for most searches/filters. The only time it fails is when your search string tried to be part of the first and last name. (ie. ‘smith, j’)

anyone else see what I am missing?

HTH

8

I think this search problem is because neither ‘first_name’ nor ‘last_name’ fields have a comma at the end. You may need to strip the comma from the search somehow, but I’m not sure how to do that.

9

jkofsky,

agreed the comma and space are the issue. I have tried to split the full_name value on the comma then trim the values and assign them to their appropriate class vars.

something to the effect of:




	public function userSearch()

	{

		$criteria = new CDbCriteria;

		if ( strlen( $this->full_name ) > 0 ) {

			if ( strstr( $this->full_name, ',' ) !== false ) {

				$tmp_name = explode( ',', $this->full_name );

				$this->user_first_name = trim( $tmp_name[1] );

				$this->user_last_name = trim( $tmp_name[0] );

			} else if ( strstr( $this->full_name, ' ' ) !== false ) {

				$tmp_name = explode( ' ', $this->full_name );

				$this->user_first_name = trim( $tmp_name[0] );

				$this->user_last_name = trim( $tmp_name[1] );

			} else {

				$this->user_first_name = trim( $this->full_name );

				$this->user_last_name = trim( $this->full_name );

			}

		}

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

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

		 -- additional criteria --

		$criteria->order = 'user_last_name, user_first_name';

		return new CActiveDataProvider('LdapUser', array('criteria' => $criteria, 'pagination' => array('pageSize' => 25)));

	}

It didn’t seem to work and I needed to move on to other areas of the site, so I didn’t really play anymore with it.

10

Unfortunately, I need filtering by both names, so your approach doesn’t work for me. I guess we have to wait for a general solution to this issue. Until then, we can just use additional columns in the DB - it denormalizes the data, but at least it works :)

11

Maybe just use http://www.yiiframework.com/doc/api/1.1/CDbCriteria#addSearchCondition-detail or http://www.yiiframework.com/doc/api/1.1/CDbCriteria#addCondition-detail instead of compare?

12

try it

$criteria->addSearchCondition(‘concat(firstname," ",lastname)’,$this->fullname);

13

The ability to use ‘like’ in the addSearchCondition may just be what I need.

Thanks Nashi and soulge

14

I just had this problem and with your help, I finally got it.

This is what I’ve done:

In the model, I’ve added a public variable:


class User extends CActiveRecord

{

	public $full_name;

Then, in public function search(), I added this:


$criteria->addSearchCondition('concat(prename, " ", name)', $this->full_name);

In rules() I added it to the ‘safe’ variables:


array('..., full_name', 'safe', 'on' => 'search'),

And I created a public function getFull_Name()


	public function getFull_Name()

	{

		return $this->prename . ' ' . $this->name;

	}

And last I’ve put it in my form-view:


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

	'id' => 'user-grid',

	'dataProvider' => $model->search(),

	'filter' => $model,

	'columns' => array(

		array(

			'name' => 'full_name',

			'value' => '$data->getFull_Name()',

		),

		'company',

		...

This is just a concluding post for all the posts before. This works. I can search for prename, name or both or parts of it etc.

Thanks!

15

Thank you very much!!