hasMany join in cakephp error

Question

I have a table jobs whose structure is similar to below :

id      desc
1       Job Description

And a table job_categories to save the categories of the jobs:

id   job_id   category_id
1    1        2
2    1        3
3    1        4

So to get the categories gor the job with id 1 , in JobsController.php, I wrote:

$similar_conditions['JobCategory.job_id'] = 1;
$similar_jobs = $this->Job->find('all',array('conditions' => $similar_conditions));

And my job model Job.php is:

class Job extends AppModel {
    var $name = 'Job';
    var $belongsTo = array('Qualification');
    var $hasMany = array('JobCategory');   
}    

But it is showing an error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'JobCategory.job_id' in 'where clause'. The query it is generating is:

SQL Query: SELECT Job.id, Job.job_title, Job.job_description, Job.job_skills, Job.contact_number, Job.contact_email, Job.qualification_id, Job.experience, Job.categories, Job.remarks, Job.support_image, Job.freshers_apply, Job.added_on, Job.status, Qualification.id, Qualification.name FROM cakead.jobs AS Job LEFT JOIN cakead.qualifications AS Qualification ON (Job.qualification_id = Qualification.id) WHERE JobCategory.job_id = 1

Why my table job_categories is not joining ?

Answer 1

From Cakephp Documentation

Foreign keys in hasMany, belongsTo or hasOne relationships are recognized by default as the (singular) name of the related table followed by _id. So if a Baker hasMany Cake, the cakes table will refer to the bakers table via a baker_id foreign key. For a multiple worded table like category_types, the foreign key would be category_type_id.

try this

var $hasMany = array(
        'JobCategory' => array(
            'className' => 'JobCategories',
            'foreignKey' => 'job_id',
            'dependent' => true,
        )
    );

Answer 2

I think the problem is here $similar_conditions['JobCategory.job_id'] = 1;. You cann't use ['JobCategory.job_id'] in your Job model.

Try this

$similar_jobs = $this->Job->find('all',array(
                        'contain' => array(
                                 'JobCategory' => array(
                                         'conditions' => array(
                                                           $similar_conditions
                                                         )
                                                   )
                                                 )
                                         )
                                      );

And add containable behavior to your AppModel