Yii framework: Using data from related Active Record models for searching

Question

Yii 1.1 application development Cookbook explain a method for using data from related Active Record Models for searching the related models as well. This method is explained in page number 193 and 194. i have tried to integrate this method in to my application but it does not work. could anybody explain me whether this feature is still available in Yii framework version 1.1.8

At this location also i could find comments for searching data form related active record models. But it also does not work. http://www.yiiframework.com/doc/api/1.1/CDbCriteria

I have order table and user table

Order table and User table has One to many Relation.

User has many orders and order has exactly one user.

So , i am editing following CDbCriterial to include user tables name and email field in to Order tables search entries.

Order table has following relations

public function relations()
{
    // NOTE: you may need to adjust the relation name and the related
    // class name for the relations automatically generated below.
    return array(
        'comments' => array(self::HAS_MANY, 'Comment', 'order_id'),
        'user' => array(self::BELONGS_TO, 'User', 'user_id'),
        'orderstatus' => array(self::BELONGS_TO, 'Orderstatus', 'orderstatus_id'),
        'commentCount' => array(self::STAT, 'Comment' , 'order_id')
    );
}

This is the search/filter conditions with user table's name filed included

public function search()
    {
        // Warning: Please modify the following code to remove attributes that
        // should not be searched.

        $criteria=new CDbCriteria;

        $criteria->compare('id',$this->id);
        $criteria->compare('order_create_date',$this->order_create_date,true);
        $criteria->compare('price',$this->price,true);
        $criteria->compare('bank_account_number',$this->bank_account_number,true);
        $criteria->compare('hardwaredetail_Id',$this->hardwaredetail_Id);
        $criteria->compare('user_id',$this->user_id);
        $criteria->compare('order_update_date',$this->order_update_date,true);
        $criteria->compare('is_received',$this->is_received);
        $criteria->compare('order_received_date',$this->order_received_date,true);
        $criteria->compare('is_notify_by_email',$this->is_notify_by_email);
        $criteria->compare('warehouse_offered_price',$this->warehouse_offered_price,true);
        $criteria->compare('warehouse_offered_price_date',$this->warehouse_offered_price_date,true);
        $criteria->compare('orderstatus_id',$this->orderstatus_id);
        $criteria->together = true; 
        $criteria->with = array('user');
        $criteria->compare('user.name',$this->user,true);
        //$criteria->compare('user.name',$this->user->name);
        return new CActiveDataProvider($this, array(
            'criteria'=>$criteria,
        ));
    }

and Order admin page is edited to display the name filed as follows

<?php $this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'order-grid',
    'dataProvider'=>$model->search(),
    //'filter'=>$model,
    'columns'=>array(
        'id',
        'order_create_date',
        'price',
        'bank_account_number',
        array(
            'name'=>'user',
            'value'=>'$data->user->name'
        ),
       ),
));

Error message returned

After solving the id column ambiguity problem by applying the solution that thaddeusmt gave i have faced with the following error message.

Thanks in advance for any help

Answer 1

It looks like both the user and order have columns named id. And your criteria uses them both in the WHERE clause, which is giving the "ambiguous" mySql error message.

When using with criteria (which does a SQL JOIN of the tables), if two of your tables have columns with the same name you need to use the mySql "dot" prefix on conditions, like so:

$criteria->compare('t.id',$this->id); // the default table prefix in Yii is "t"

When I JOIN a table using $criteria->with I prefix all of the column names (in my compare and condition criteria, etc)., like so:

$criteria->compare('t.id',$this->id); // t
$criteria->compare('t.order_create_date',$this->order_create_date,true); // t
$criteria->with = array('user');
$criteria->compare('user.name',$this->user_id,true); // user (the filter will set user_id)

Your gridview will need to look like this:

array(
  'name'=>'user_id',
  'header'=>'User',
  'sortable'=>false,
  'value'=>'$data->user->name'
),

Also, I think there is a larger problem, as you point out with your edit:

Your search function is set up like this: user.name',$this->user - but $this->user is going to return the current user object via the relation, not the search criteria. The column filter will set the user_id, property.

EDIT: Nope, you can $this->user as your column name so long as you set it as a safe attribute.

The way I am getting around this is shown in more detail here:

Search in BELONGS_TO model column with CGridView, Yii

Sorting will not work with that though - just the filtering.

Here is good post in the Yii forum that might give you more clues, too:

http://www.yiiframework.com/forum/index.php?/topic/9083-search-filter-of-a-relations-field-through-cgridview/

Sadly, sorting and filter CGridViews on relations is not really default functionality. YOu can easily display related info with a column name like user.name, but it won't sort or filter. Good luck!

Answer 2

I could find the answer. This is all i did.

Following are the two tables i have. Order and User

I have Order/Admin page, Default generated code provide searching order table data only. i want to relate user tables name field in the search criteria.

This is the initial look of the search page.

I want to integrated user name filed from other table in to this search. then final look will be as follows.

so these are the steps i did.

first in the Order model i have following relations

    public function relations()
        {
            // NOTE: you may need to adjust the relation name and the related
            // class name for the relations automatically generated below.
            return array(

                'user' => array(self::BELONGS_TO, 'User', 'user_id'),


            );
        }
This is generated by Yii framework , i did nothing :)

Then , i changed the search method as follows

public function search()
    {
        // Warning: Please modify the following code to remove attributes that
        // should not be searched.

        $criteria=new CDbCriteria;


        $criteria->compare('t.order_create_date',$this->order_create_date,true);
        $criteria->compare('t.price',$this->price,true);
        $criteria->compare('t.bank_account_number',$this->bank_account_number,true);
        $criteria->compare('t.hardwaredetail_Id',$this->hardwaredetail_Id);
        //$criteria->compare('user_id',$this->user_id);

        $criteria->compare('t.order_update_date',$this->order_update_date,true);
        $criteria->compare('t.is_received',$this->is_received);
        $criteria->compare('t.order_received_date',$this->order_received_date,true);
        $criteria->compare('t.is_notify_by_email',$this->is_notify_by_email);
        $criteria->compare('t.warehouse_offered_price',$this->warehouse_offered_price,true);
        $criteria->compare('t.warehouse_offered_price_date',$this->warehouse_offered_price_date,true);
        $criteria->compare('t.orderstatus_id',$this->orderstatus_id);
        $criteria->together = true; 
        $criteria->compare('t.id',$this->id,true);
        $criteria->with = array('user');
        $criteria->compare('name',$this->user,true,"OR");
        return new CActiveDataProvider($this, array(
            'criteria'=>$criteria,
        ));
    }

it is important to put t in-front of the t if your Order table primary key field if both have save name. in my case it is id and id, so i had to put t.

Other thing is the order of the elements

$criterial->togeter = true; should come before the relational elements.

then u updated to rules method in Order table. i added user filed and name filed to safe attributes.

public function rules()
    {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            //array(' orderstatus_id', 'required'),
            array('hardwaredetail_Id, user_id, is_received, is_notify_by_email, orderstatus_id', 'numerical', 'integerOnly'=>true),
            array('price, warehouse_offered_price', 'length', 'max'=>10),
            array('bank_account_number', 'length', 'max'=>100),
            array('order_create_date, order_update_date, order_received_date, warehouse_offered_price_date, user,name', 'safe'),
            // The following rule is used by search().
            // Please remove those attributes that should not be searched.
            array('id, order_create_date, price, bank_account_number, hardwaredetail_Id, user_id, order_update_date, is_received, order_received_date, is_notify_by_email, warehouse_offered_price, warehouse_offered_price_date, orderstatus_id', 'safe', 'on'=>'search'),
        );
    }

Finally update your UI code.

<?php $this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'order-grid',
    'dataProvider'=>$model->search(),
    'filter'=>$model,
    'columns'=>array(
        'id',
        'order_create_date',
        'price',
        'bank_account_number',
        array(
            'name'=>'user',
            'value'=>'$data->user->name'
        )
)); ?>

i updated the order admin with the

array(
                'name'=>'user',
                'value'=>'$data->user->name'
            )

That is what i did and it worked for me. ask me if you need any help. Thanks every one looking in to this issue.