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