I have table columns like below
id,
email,
password
created_by
here my ‘created_by’ column points to the same table ‘id’ column
How can i establish relationship here in order to display user email who has created, instead of displaying values.
Regards,
Pavan
At a rough guess, something like the following should work I think:
(as a line in the relations array assuming the table is called user)
'createdUser' => array(self::BELONGS_TO, 'User', 'created_by'),
You can then refer to $model->createdUser rather than $model->created_by.
If there are ambiguation problems, then try adding an alias (‘alias’ => ‘userCreated’) to the array.
My Model class is like below as of now
class UserProfile extends CActiveRecord
{
public function relations()
{
'createdUser' => array(self::BELONGS_TO, 'UserProfile', 'created_by'),
}
}
When i use in CGridView like
array(
'name'=>'created_by',
'value'=>'$data->createdUser',
),
its throwing an error
My Table name is user_profile which i have columns id,email,password,created_by where ‘created_by’ column link to ‘id’ column in the same table. I want to view email instead of id in the grid.
Could some one help me out on this.
Creating the relation createdUser means that you can use $model->createdUser to refer to the User instance that corresponds to the created_by id - the important thing here being that it is an instance of the User model and not just a text field.
So, if you wanted to display the email of the user who created this user, then you would use the following in your CGridView columns:
array(
'name' => 'created_by',
'value' => $data->createdUser->email,
),
(note that as far as I am aware, you need to do this in an array since you are customising the value to appear, you can’t just do it as ‘created_by’ => $data->createdUser->email).
For future reference, when you say that errors are thrown, you should mention exactly what error is thrown here since that will usually help in determining the problem.
Above solution worked for me. I have few more questions in my CGridView, how can i search with email in created by field. My model class search function is like below
public function search()
{
$criteria=new CDbCriteria;
$criteria->compare('id',$this->id);
$criteria->compare('email',$this->email,true);
$criteria->compare('createdUser.email',$this->createdUser->email);
return new CActiveDataProvider(get_class($this), array(
'criteria'=>$criteria,
));
}
What should i do to the above function in order to search my created by column according to the email.
Are you talking about searching or sorting?
If it’s searching, then I think what you have done should be ok.
If it’s sorting, then you need to create a custom sort for the CActiveDataProvider that your search function returns like so (if you change the attributes array, you have to add each attribute that you want to be sortable, manually):
$sort = new CSort();
$sort->attributes = array(
'id',
'email',
'created_by' => array(
'asc' => 'createdUser.email',
'desc' => 'createdUser.email DESC',
),
),
);
then instead of the default CActiveDateProvider, you return:
return new CActiveDataProvider(get_class($this), array(
'criteria' => $criteria,
'sort' => $sort,
));
Be careful here though because you are confusing created_by (which is an actual attribute of your UserProfile model) and ‘created_by’ which is the name of a custom variable you have made available in your CGridView - it would be better to change it to something different in both.
I am looking for searching here, i am not able to search in the field.
Here my code goes
My View
<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'user-profile-grid',
'dataProvider'=>$model->search(),
'filter'=>$model,
'columns'=>array(
'id',
'email',
array(
'name'=>'created_by',
'value'=>'$data->createdUser->email',
),
array(
'class'=>'CButtonColumn',
),
),
)); ?>
My Model Class
class UserProfile extends CActiveRecord
{
public function rules()
{
array('id, email, created_by', 'safe', 'on'=>'search'),
}
public function relations()
{
'createdUser' => array(self::BELONGS_TO, 'UserProfile', 'created_by'),
}
public function search()
{
$criteria=new CDbCriteria;
$criteria->compare('id',$this->id);
$criteria->compare('email',$this->email,true);
$criteria->compare('createdUser.email',$this->createdUser->email);
return new CActiveDataProvider(get_class($this), array(
'criteria'=>$criteria,
));
}
}
Is there anywhere i am wrong.
Yes, as far as I am aware, you won’t be able to search like this, because the search functions in a similar way to updating a model - a model instance is created, all the form variables are massively assigned to it, then a search is made using those variables. Since you would effectively be assigning an email address to created_by (which is just an id) then it won’t work.
To get around this, depends on how you want to filter - is a dropdown box with a list of all email addresses from which you can select the one you want ok, or do you need a text box in which to type a partial match of an email address?
If a dropdown is ok, then simply replace your column with the following:
array(
'name' => 'created_by',
'filter' => CHtml::listData(UserProfile::model()->findAll(), 'id', 'email'),
'value' => '$data->createdUser->email',
),
This simply specifies the filter for the header cell (replacing a text box with a dropdown box) of the ‘created_by’ column, and replaces the id value that would be taken from ‘created_by’ with the email address from the relation.
If, on the other hand, you need to filter using a partial text, then it becomes more complicated…
I want to go with the text box search.
Ok, well this is one way I think (I’m not saying it is the only way)…
You’ll need to add a custom getter and setter to your model, as well as an attribute, for the search parameter, like so:
private _createdEmail = null;
public function getCreatedEmail() {
if ($this->_createdEmail === null) {
if ($this->created_by === null) {
return null;
}
else {
$this->_createdEmail = $this->createdUser->email;
}
}
return $this->_createdEmail;
}
public function setCreatedEmail($value) {
$this->_createdEmail = $value;
}
You’ll need to add a rule to your rules array to make this parameter safe for massive assignment, like so:
array('createdEmail', 'safe'),
Now you add this to your search function, like so:
if ($this->_createdEmail !== null) {
$criteria->compare('createdUser.email',$this->_createdEmail);
}
And hopefully, all that remains is to use the pseudo attribute in your GridView columns array:
'createdEmail',
You might also want to add it to your attributeLabels array if you want a pretty column title automatically:
'createdEmail' => 'Created By',
To summarize, what you are doing is adding a pseudo attribute to your UserProfile model, which will be the email address of the user who created it (or null if created_by is not set). This means that you can use it everywhere like you would a normal attribute. By adding a safe rule to it, you allow it to be massively assigned from GET/POST forms, which means that the search model can process it. The search function then adds it as a search criteria if it is populated (it might skew search results if not populated), and voila!
I hope this does it for you.
Hi RedRabbit , I followed your instructions but I had some errors.
if ($ this-> _createdEmail! == null) {
$ criteria-> compare ('createdUser.email', $ this-> _createdEmail);
}
That piece of code fails. When doing the search did not find the field ‘createdUser.email’. Here I have chosen to put the original field “created_by.” Then, instead of $ this-> _createdEmail. Here’s the code:
if ((trim ($ this-> _createdEmail)! == "")) {
$ id_email = UserProfile:: model () -> findByAttributes ((array ('email' => $ this-> _createdEmail)));
if ($ id_email! == null) {
$ criteria-> compare ('id_email', $ id_email->id, true);
}
}
I do not understand because the search is complete if I do it, but not partial search.
Could you help me?
Thanks
I answer myself. Need to put in the criteria
$criteria->with=array('CreatedUser');
That was all. I hope this can help someone.
Greetings.
hello again, I everything works correctly. The only problem is that when a field is sorted by then the filter does not work.
could anyone help me?
thanks alot.
Sorry about that, and sorry for the delay.
I think I made a slight mistake in the getter/setter.
so instead of what I gave you, you should put:
private _createdEmail = null;
public function getCreatedEmail() {
if ($this->_createdEmail === null) {
if ($this->created_by === null) {
return null;
}
else {
return $this->createdUser->email;
}
}
else {
return $this->_createdEmail;
}
}
public function setCreatedEmail($value) {
$this->_createdEmail = $value;
}
So this only actually sets the private attribute if it is massively assigned.
I’m not sure why setting the ‘with’ attribute for the search criteria changed something - it will certainly not be the answer to your problems - all it should change is whether the details for CreatedUser are loaded in the original search query or in a separate query (see lazy versus eager loading), although sometimes it can change the search results for complicated join queries.
However, as long as your relation ‘createdEmail’ is defined properly, you should not get that error.
Try with the new getter I gave you above (along with the original code I gave), and let me know if things work better.
Works perfectly. Thanks for your help.
My stupid mistake was that I also put inside the attribute createdEmail
array(’…, createdEmail’,’ safe ‘,’ on ‘=>’ search ')
instead of
array (‘createdEmail’, ‘safe’)
Again, thank you very much for your time.
Best regards.