Yii - Joining multiple tables

Question

i am trying to join 3 tables in my model

trx_evaluation_details

trx_evaluation

rm_projects

trx_evaluation_details and trx_evaluation are already joined by using the relation function in my model.

'eval' => array(self::BELONGS_TO, 'Evaluation', 'eval_id'),

i am trying to join the rm_projects table so that i can access the project_name column in that table so i added this.

'project' => array(self::BELONGS_TO, 'RmProjects', 'project_id'),

so i have this relation in my model..

public function relations() {
        return array(                
            'eval' => array(self::BELONGS_TO, 'Evaluation', 'eval_id'),
            'project' => array(self::BELONGS_TO, 'RmProjects', 'project_id'),
        );
    }

i tried to access it like this..

if ($search_date_end !== '' && $search_date_start !== '' && $search !== '') {
                    $criteria->condition = "start_date  >= '$search_date_start' 
                    AND end_date <= '$search_date_end' 
                    AND project.project_name like '%$search%'
                    AND t.employee_id = '$employee->company_id'"; 
            }

where i tried project.project_name.. but it is returning an error

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'project.project_name' in 'where clause'. The SQL statement executed was: SELECT COUNT(DISTINCT t.id) FROM trx_evaluation_details t LEFT OUTER JOIN trx_evaluation eval ON (t.eval_id=eval.id) WHERE (start_date >= '2015-11-01' AND end_date <= '2015-12-01' AND project.project_name like '%sprobe%'AND t.employee_id = '120069')

which means that it cannot see project.project_name and the table rm_projects is not joined in the returned error.

how can i access the project_name and how can i join the rm_projects table.? please help.

I've answered you in previouse question stackoverflow.com/questions/33949387/… — SiZE

AddWeb Solution Pvt Ltd AddWeb Solution Pvt Ltd · Accepted Answer · 2015-12-02T14:11:52

I have tried to solve your issue in the Model's search() method and made following changes. It works fine.

$criteria->with = array('eval', 'project');

if ($search_date_end !== '' && $search_date_start !== '' && $search !== '') {
        $criteria->condition = "start_date  >= '$search_date_start' 
        AND end_date <= '$search_date_end' 
        AND project.project_name like '%$search%'
        AND t.employee_id = '$employee->company_id'"; 
}

Please let me know, if you have any concern with this.

Thanks.

Yii - Joining multiple tables

2 Answers