Join tables in Yii

Question

I have a query like this:

SELECT jo . * , pc.checklist_id FROM job_order_candidates jo, pdpa_checklist pc WHERE jo.job_id = '5755' AND jo.shortlisted = '1' AND pc.job_id = '5755'

I need to write it in Yii format, how to do it? At the moment I have this code:

$dataProviderSL=new CActiveDataProvider('JobApplication', array(
                'criteria'=>array(
                        'condition'=>'shortlisted='.JobApplication::SL_Y.' AND job_id='.$jobOrder->job_id,
                        'order'=>'applied_date DESC',
                        'with'=>array('candidate'),
                ),
                'pagination'=>array(
                        'pageSize'=>20,
                ),
        ));

It's only select single table, I need to select 2 tables. Can anyone help me? Thanks alot.

score 1 · Answer 1 · answered Jul 31 '14 at 14:56

It's hard to find the correspondance bewteen your ActiveRecords and your table but you should try something like:

$dataProviderSL=new CActiveDataProvider('JobApplication', array(
   'criteria'=>array(
       'condition'=>'shortlisted= :shortlisted',
       'order'=>'applied_date DESC',
       'params' => array(
           ':shortlisted' => JobApplication::SL_Y,
           ':job_id' => $jobOrder->job_id,
       ),
       'with'=>array('candidate' => array(
           'together' => true, 
           'condition' => 'job_id= :job_id'
       )),
    ),
    pagination'=>array(
        'pageSize'=>20,
    ),
));

I'm note sure if job_id is in the relation or the main model, if you want a more precise answer you have to provide us more infos.

score 0 · Answer 2 · answered Jul 31 '14 at 19:41

That SQL does not make sense if you are to use CActiveDataProvider philosophy.

CActiveDataProvider would provide you with an array of JobApplication models, which should be stored on the rows of your job_order_candidates table. Since pc.checklist_id belongs to another table, you should have another model that represents those entries, and on your JobApplication class you need a relation with that model. Assuming it would be called PdpaChecklist, your JobApplication would look like this:

class JobApplication extends CActiveRecord {
    // your stuff

    public function relations() {
        return array(
            'pdpaChecklist' => array(self::HAS_ONE, 'PdpaChecklist', 'job_id'),
        );
    }
}

That given, you can query JobApplications as you currently do and access that pc.checklist_id like the following:

foreach ($dataProviderSl->getData() as $jobApplication) {
    $jobApplication->pdpaChecklist->checklist_id;
}

Since you will be iterating through JobApplications, it is a good idea to use eager loading, preventing Yii to run a query everytime you do $jobApplication->pdpaChecklist. To do so, include 'pdpaChecklist' to your 'with' statement.

'with'=>array('candidate', 'pdpaChecklist'),

That is probably more work than you would have imagined, but that is how Yii organizes things. When you synergizes with the phylosophy it goes mych easily and faster. Totally worth it.

Join tables in Yii

2 Answers2