CGridView clear other columns when i filter

Hello,

This is my first post in this forum. Since its going to be very long, I’ll partition the post into sections problem in full details and procedure).

[size="5"]Problem[/size]

I have a joined two tables together (there are more but i don’t touch others so no worries). Here is their structures:

Entity -> id(PK), first, middle, last, dob, etc.

Attributes -> id(PK), entity_id(FK to Entity.Id), name, value, date

attributes->(1, 1, ‘phone’, ‘2341411241’, ‘NOW()’), (2,1,‘email’,‘test@test.com’, ‘NOW()’), etc etc…

I made it that the attributes.name rows are columns in the grid view and the data for these columns are attributes.name

Everything about showing this stuff works perfect. but when i start doing the filtering it clears all the other columns

[size="5"]Procedure[/size]

Basically thats what i did. I used gii to generate both models. Because when gridview sees "." in the attributes it just blocks filtering i had to go with the virtual attributes method. What i basically did is i made a static variable $attributes and stores fetched the data from attributes to that in the constructor:




static::$attrs = Yii::app()->db->createCommand('select `name` from `tblAttributes` group by `name`')->queryAll();



this gets me ‘phone’, ‘email’, etc. in that static variable

then in order to make things easier i make another private array vAttributes (aka virtual attributes) and define its keys in the c’tor:




foreach(static::$attrs as $attr) {

	$this->vAttributes[$attr['name']] = null;		

}




Then i made two helper functions to make my life easier :)




private function _getAttrib($attrib) {

	if($this->vAttributes[$attrib] == null && isset($this->tblAttributes)) {

		foreach($this->tblAttributes as $attr) {

			if($attr->name == $attrib) {

				$this->vAttributes[$attrib] = $attr->value;

				break;

			}

		}

	}

		

	return $this->vAttributes[$attrib];


}


private function _setAttrib($attrib,$value) {

	$this->vAttributes[$attrib]=$value;

}



so basically if i need for example phone attribute i just type:




public function getPhone() {

	return $this->_getAttrib('phone');	

}

	

public function setPhone($phone) {

	$this->_setAttrib('phone',$phone);	

}



search rules and columns are all generated automatically:




public function rules() {

        $searchrule = array('first, middle, last, username, dob, fullname', 'safe', 'on'=>'search');

        foreach(self::$attrs as $attr) {

	        $searchrule[0] .= ', '. $attr['name'];

        }


        //other rules... and returning array

}


public function vAttribCols() {

	

	$cols = array();

	foreach(self::$attrs as $attr) {

       	$capitalize = ucfirst($attr['name']);

        	

       	if(method_exists($this, 'get'.$capitalize) && method_exists($this, 'set'.$capitalize)) {

			$cols[] = $attr['name'];

		}		

	}

		

	return $cols;

	

}




and thats the search function where the problem arises:




foreach(self::$attrs as $attr) {

       	$capitalize = ucfirst($attr['name']);

        	

       	if(method_exists($this, 'get'.$capitalize) && method_exists($this, 'set'.$capitalize)) {

			$criteria->compare(	'`tblAttributes`.`name`="'.$attr['name'].'" and `tblAttributes`.`value`',

						$this->$attr['name'], true, 'or');

							

	}

}



so, if you don’t have the getter and setter its not showing it and looking for it.

the problem happens in the criteria->compare(). this criteria’s query looks something like that:




select 

    `first`, `name`, `value`, `date`

from

    `ecrm`.`tblEntity` `t1`

        left outer join

    `ecrm`.`tblAttributes` `t2` ON `t1`.`id` = `t2`.`entity_id`

where

    `t2`.`name`='attachment' and `t2`.`value` like '%test%' or

	`t2`.`name`='phone' and `t2`.`value` like '%'


order by `t2`.`date` desc



which actually executes and gives me the desired result. What can i do to solve that issue?

Thanks in advance.

Gasim

since its my first post im not allowed to share links. Here are the screenshots. first one is without filtering. the second one is with filtering. notice, that the mobile, phone field in entity "Ubuntu Maverick Linux" exists (from first picture) but in the second one they are cleared out.

It seems complicated to me…!

But if you say that the search function is generating the correct SQL (you’ve tested the SQL and it gives you the results you want), then I would have to say that the problem must be in your display parameters for CGridView, since the data must be there in the dataProvider, just not displayed…

Are you really sure that the SQL is correct though? How have you obtained it? Are you looking at the debug log on the page, or in the ajax request?

Ohh. sorry couldn’t explain it. I know that the problem is in yii search sql statement. the sql itself works but in yii it doesn’t and its happening to me second time already :( I was just looking for something. I think i found the solution. will update here the result

Thanks,

Gasim

No luck. :( I can’t get this to work.

So where exactly are you getting this SQL from?

SQL is actually wrong :( and i can’t get to fix it.

You’re not being very explicit here - just saying it doesn’t work does not tell us how we can help you.

Where exactly did you get this SQL from, and what error does it generate?

I’m still not sure that I’ve completely understood what you are trying to do, but if I have, then I think there is an easier way to go about it.

You should define relations in your Entity model for each of the attributes that you want to use, e.g.


'phoneAttribute' => array(self::HAS_ONE, 'Attributes', 'entity_id', 'condition' => 'name = \'phone\''),

'emailAttribute' => array(self::HAS_ONE, 'Attributes', 'entity_id', 'condition' => 'name = \'email\''),

This does assume that each entity may only have one of each attribute (hence HAS_ONE rather than HAS_MANY), but I presume that this must be the case, since if not you could potentially have multiple lines for one entity in your grid, which is probably not what you want - therefore you should also have some king of unique constraint in your database on the entity_id and name fields in your Attributes table.

You can then create variables with getters and setters for the model, like:


protected $_phone;

protected $_email;


public function getPhone() {

    if (!isset($this->_phone)) {

        if ($this->phoneAttribute === null) {

            $this->_phone = null;

        }

        else {

            $this->_phone = $this->phoneAttribute->value;

        }

     }

    return $this->_phone;

}


public function setPhone($value) {

    $this->_phone = $value;

}


public function getEmail() {

    if (!isset($this->_email)) {

        if ($this->emailAttribute === null) {

            $this->_email = null;

        }

        else {

            $this->_email = $this->emailAttribute->value;

        }

     }

    return $this->_email;

}


public function setEmail($value) {

    $this->_email = $value;

}

You will also need to add rules to make these variables safe for massive assignment:


array('phone', 'safe'),

array('email', 'safe'),

You can now use phone and email just as if they were attributes of the Entity model (although obviously you will not be able to change their value in this way).

In your search function, you then add:


if (isset($this->_phone)) {

    $criteria->compare('phoneAttribute.value', $this->_phone, true);

}

if (isset($this->_email)) {

    $criteria->compare('emailAttribute.value', $this->_email, true);

}

Not perfect, but I think it should allow you to do what you are looking for.

OMG. I haven’t thought of that. I am now checking it out. Thanks!

Actually, now I come to think of it, you don’t want to set the protected variables in the getters, just use:


public function getPhone() {

    if (isset($this->_phone)) {

        return $this->_phone;

    }

    else {

        if ($this->phoneAttribute === null) {

            return null;

        }

        else {

            return $this->phoneAttribute->value;

        }

    }

}

Oh. This thing works but it has a lot of problems like sorting, searching. i don’t understand whats happening but as i open the gridview page the default number is page size is 20 but it should be 25:

"Displaying 1-19 of 10013 result(s)." <— thats whats written in the description part of gridview.

And when i filter a username thats whats actually written in the gridview:

"Displaying 1-2 of 6 result(s)."

Why is this happening?? i just did the code you gave me.

And one more thing. when i disable sorting it shows only one result :confused:

Thanks,

Gasim

For the page size, you’ll have to set that when you create the CActiveDataProvider in the search function.

You can try adding the together property too, so that your count query works out ok.

As for sorting - you’ll have to supply the CSort object to your CActiveDataProvider to allow it to sort by your custom attributes.

All the above adds the following to your search function:


$criteria->together = false;


$sort = new CSort();

$sort->attributes = array(

	'phone' => array(

		'asc' => 'phoneAttribute.value',

		'desc' => 'phoneAttribute.value DESC',

	),

	'email' => array(

		'asc' => 'emailAttribute.value',

		'desc' => 'emailAttribute.value DESC',

	),

	'*',

);


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

	'criteria' => $criteria,

	'pagination' => array(

		'pageSize' => 25,

	),

	'sort' => $sort,

));

Beyond that, if it is still not working as you expect, then you will need to use the logs to find out what SQL it is using, and therefore where it is going wrong. If you do not post that kind of information, I cannot help you!

I had that.




system.db.CDbCommand.query(SELECT `t`.`id` AS `t0_c0`, `t`.`first` AS `t0_c1`, `t`.`middle` AS `t0_c2`, `t`.`last` AS `t0_c3`, `t`.`document` AS `t0_c4`, `t`.`dob` AS `t0_c5`, `t`.`username` AS `t0_c6`, `attribAttachment`.`id` AS `t3_c0`, `attribAttachment`.`entity_id` AS `t3_c1`, `attribAttachment`.`name` AS `t3_c2`, `attribAttachment`.`value` AS `t3_c3`, `attribAttachment`.`date` AS `t3_c4`, `attribMobile`.`id` AS `t4_c0`, `attribMobile`.`entity_id` AS `t4_c1`, `attribMobile`.`name` AS `t4_c2`, `attribMobile`.`value` AS `t4_c3`, `attribMobile`.`date` AS `t4_c4`, `attribPhone`.`id` AS `t5_c0`, `attribPhone`.`entity_id` AS `t5_c1`, `attribPhone`.`name` AS `t5_c2`, `attribPhone`.`value` AS `t5_c3`, `attribPhone`.`date` AS `t5_c4` FROM `tblEntity` `t` LEFT OUTER JOIN `tblAttributes` `attribAttachment` ON (`attribAttachment`.`entity_id`=`t`.`id`) AND (`attribAttachment`.`name`='attachment') LEFT OUTER JOIN `tblAttributes` `attribMobile` ON (`attribMobile`.`entity_id`=`t`.`id`) AND (`attribMobile`.`name`='mobile') LEFT OUTER JOIN `tblAttributes` `attribPhone` ON (`attribPhone`.`entity_id`=`t`.`id`) AND (`attribPhone`.`name`='phone') ORDER BY t.username asc, `attribAttachment`.`date` desc, `attribMobile`.`date` desc, `attribPhone`.`date` desc LIMIT 25)

	

system.db.CDbCommand.query(SELECT COUNT(DISTINCT `t`.`id`) FROM `tblEntity` `t` LEFT OUTER JOIN `tblServicesMap` `tblServicesMaps` ON (`tblServicesMaps`.`entity_id`=`t`.`id`) LEFT OUTER JOIN `tblServices` `service` ON (`tblServicesMaps`.`service_id`=`service`.`id`) LEFT OUTER JOIN `tblAttributes` `attribAttachment` ON (`attribAttachment`.`entity_id`=`t`.`id`) AND (`attribAttachment`.`name`='attachment') LEFT OUTER JOIN `tblAttributes` `attribMobile` ON (`attribMobile`.`entity_id`=`t`.`id`) AND (`attribMobile`.`name`='mobile') LEFT OUTER JOIN `tblAttributes` `attribPhone` ON (`attribPhone`.`entity_id`=`t`.`id`) AND (`attribPhone`.`name`='phone') )	


system.db.CDbCommand.query(select `name` from `tblAttributes` group by `name`)	

system.db.CDbCommand.query(SHOW COLUMNS FROM `tblEntity`)	

system.db.CDbCommand.query(SHOW COLUMNS FROM `tblAttributes`)	

system.db.CDbCommand.query(SHOW COLUMNS FROM `tblServicesMap`)	

system.db.CDbCommand.query(SHOW COLUMNS FROM `loginRoles`)	

system.db.CDbCommand.query(SHOW COLUMNS FROM `tblServices`)	


system.db.CDbCommand.query(SELECT `t`.`id` AS `t0_c0`, `tblServicesMaps`.`id` AS `t1_c0`, `tblServicesMaps`.`entity_id` AS `t1_c1`, `tblServicesMaps`.`service_id` AS `t1_c2`, `service`.`id` AS `t2_c0`, `service`.`type` AS `t2_c1`, `service`.`description` AS `t2_c2` FROM `tblEntity` `t` LEFT OUTER JOIN `tblServicesMap` `tblServicesMaps` ON (`tblServicesMaps`.`entity_id`=`t`.`id`) LEFT OUTER JOIN `tblServices` `service` ON (`tblServicesMaps`.`service_id`=`service`.`id`) WHERE (`t`.`id` IN (5335, 1976, 9706, 4618, 6101, 4903, 9497, 369, 8212, 8884, 8012, 3498, 7030, 174, 2087, 8822, 4719, 6455, 3379)))



this is the query i get from sql profile summary.

and this is my code:





$criteria=new CDbCriteria;


		$with = array('tblServicesMaps'=>array('with'=>'service'));


		$sort = new CSort();

		$sort->defaultOrder = array('username'=>false);

		$sort->attributes = array(

			'username'=>array('asc'=>'t.username asc', 'desc'=>'t.username desc'),	

			'dob'=>array('asc'=>'t.dob asc', 'desc'=>'t.dob desc'),

			'fullname'=>array(	'asc'=>"CONCAT(first, ' ', middle, ' ', last) asc", 

								'desc'=>"CONCAT(first, ' ', middle, ' ', last) desc"),

		);


		$criteria->together = false;

	

 		$criteria->compare("CONCAT(first, ' ', middle, ' ', last)", $this->fullname, true);

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


//this is just auto-generation. it does exactly what the other attachment and phone does.

//I am pretty sure. Got the same result with it.


        foreach(self::$attrs as $attr) {

        	$capitalize = ucfirst($attr['name']);

			$name = 'attrib'.$capitalize;

        	if(method_exists($this, 'get'.$capitalize) && method_exists($this, 'set'.$capitalize)) {

				$criteria->compare($name.'.value', $this->$attr['name'], true);

				$with[$name]=array();

			}

		}

		

		$criteria->with=$with;

		

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

			'criteria'=>$criteria, 'pagination'=>array('pageSize'=>25), 'sort'=>$sort

		));



Bump. Anyone?

You don’t seem to be following what I suggested, and haven’t given your view code, so it is a little hard to say more.

From your SQL it looks like the together = false is not being taken into account since everything is being fetched in one big query, although at a quick glance I haven’t noticed anything wrong with the actual SQL (apart from the fact that it will generate a lot of lines per entity - do you actually get an error message when you use that SQL manually?). That being said, according to the documentation, together is only valid for relational queries, which is another reason you should be trying it my way…







echo '<form name="gridForm" method="post" action="">'."\n";


$columns = array(

			array(	'class'=>'CCheckBoxColumn',

			'selectableRows'=>25,

			),

			'fullname',

			'username',);

foreach($model->vAttribCols() as $cols) {

	$columns[] = $cols;


}

$columns[] = array(	'header'=>'Services', 'type'=>'raw', 'value'=>'$this->grid->getOwner()->parseServicesArray($data)');

$columns[] = array(	'header'=> 'Actions', 

			'class'=>'application.components.CDataButtonColumn',

			'template'=>'{view}{update}',

			'buttons'=>array(

				'view'=>array('url'=>'"/entity/view/".$data->id'),

				'update'=>array('url'=>'"/entity/view/".$data->id."#edit"'),

			),

			'footer'=>'

				<a href="javascript:alert(\'This is going to be used for multiple selections\')"><img src="/assets/11aac01b/gridview/view.png"/></a> 

				<a href="javascript:alert(\'This is going to be used for multiple selections\')"><img src="/assets/11aac01b/gridview/update.png"/></a>

					',

			

			'filter'=>'

				<a href="javascript:alert(\'This is going to be used for multiple selections\')"><img src="/assets/11aac01b/gridview/view.png"/></a> 

				<a href="javascript:alert(\'This is going to be used for multiple selections\')"><img src="/assets/11aac01b/gridview/update.png"/></a>

				',

			

		);

		

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

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

'filter'=>$model,

'pager'=>array(

	'class'=>'CLinkPager',

),

'columns'=> $columns


));

	


echo '</form>';




So, I should separate all the queries? I’ll check the query in raw sql

All of that looks ok. There are a couple of points, which are nothing to do with your problem:

Why are you creating a form? Is this just so that the filter inputs are wrapped in a form for validation?

It seems to me that you are not taking enough advantage of Yii’s helpers for your Html - you could use CHtml::beginForm for your form tag (this would automatically include CSRF codes if you had CSRF enabled for example).

You could use CHtml::link to create anchors, with the many options that go with that, and CHtml::image for the image to be included in the anchor, all using Yii generated Urls (thereby avoiding any issues if you change the location of your application).

Also for the images you are using for your custom buttons, you should use Yii::app()->getAssetManager()->publish(Yii::getPathOfAlias(‘zii.widgets.assets.gridview’)).‘view.png’ as the path, since the assets directory has a random structure that will change every time you empty it or move your application - you should never use an absolute path to access assets folders.

Why do you define the pager in your GridView - it is defined as a CLinkPager by default?

All that aside, I really don’t see why you want to have such a complicated system for accessing these pseudo attributes - you can’t avoid some hand-coding if what you want to do is work. For your sorting/filtering goodness, the attributes that you want to access will need to exist as a relation in your Entity model, which means that you’ll have to code each one into your model (if you really want to do loops, then you could define your relations in a loop, but you would have to use an array of attributes hard-coded by yourself, or do a custom SQL lookup of all the available attributes beforehand, which sort of defeats the purpose, since an instance of a model cannot know whether the attributes it has include all the different ones available. You could even then override the magic getters and setters to automate getting and setting of your attributes, but I think this is just making things far too complicated.

Hard code an array of your attributes into your model if you want to loop.

Create a relation for each different attribute.

Code a getter and a setter for each different attribute.

Loop through the attributes to add them into your search criteria and sort criteria in your search function (but I would only include them in the search criteria if they have a value set, since otherwise this might interfere with Entities who don’t have this related attribute).

Loop through the attributes in your view to add them as columns to the GridView.

For form and the CLinkPager thing. I am going to change that to an extended CLinkPager where its going to store basic pager, alpha pager, and pageSize changer. So I put the form and actually forgot to remove it because I haven’t used any other file other than the Model Class for the past 3 days :P

Thanks for the advice. I’ll change them to Yii functions. Never knew whats the advantage but now it makes sense.

I made a static variable self::$attributes which gets the attributes by lookup. And another local array which gets the ones they can have. Basically I say “If phone exists for the user array(‘phone’)=phone. If not it equals to null.” Then I do the checkings in the search function (if phone != null { … } ).

I am actually doing all that. The only thing i don’t have is overriden magic getter and setter functions. The system loops through the array and inserts that its safe search, adds the relation to the entity (i just made a format: ‘attrib’.ucfirst($attribute_name) ), creates the columns, and searches. I know for sure, though that the sql has a problem:

In your previous posts you posted this:

Here you are using ‘condition’. I first started with that but it didn’t work at all (weird results). So, i changed it to ‘on’. It worked. But the searching just doesn’t want to work :( And the together thing bothers me a lot. I’ll first change the relations to Eager Loading. Then I’ll let you know about what happens if i use raw sql.

I don’t even know honestly. I feel like this system is not going to work. You can’t build this system with one query. I am thinking maybe I should make multiple queries and kind of merge them somehow and see the result. But the performance is the issue too. It needs to open in at most a couple of seconds because one of the frequently used page in that system is that page - All the operators/workers use that page and they have to see every value possible for each entity in one table… And its going to be refreshed almost every second.

Thanks for the help,

Gasim

Yes, you’re right about using ‘on’ rather than ‘condition’ in your relations - sorry I forgot about that.

What is it that bothers you about ‘together’? As far as I can see you will need it, because in a CGridView one row generally corresponds with one model instance (although this obviously depends on the data provider), but I think this is what you want anyway.

I haven’t delved into the source code for ‘together’, but according to the docs, you will need to use relations to be able to use it. You’re right - this system won’t work with one query, but that is the whole point of setting ‘together’ to false - it then makes queries to each join table, using the results to populate a results array with one line per entity - thereby collating all the data you want but eliminating the duplicate rows caused by the query type.

Ok. I’ll try that and see where I can get.