CakePhp 2.5 $belongsTo in two models

Question

Right now I have this: Groups Inside Groups we have Subgroups Inside Subgroups we have Comments

Subgroups belongs to Groups 1, 2, 3 ; subgroups table have group_id field Comments belongs to Subgroups A, B, C ; comments table have subgroup_id field

My models:

CommentsGroup.php

<?php

App::uses('AppModel', 'Model');

class CommentsGroup extends AppModel {

    public $useTable = 'comment_groups';

}

CommentsSubGroup.php

<?php

App::uses('AppModel', 'Model');

class CommentsSubGroup extends AppModel {

    public $useTable = 'comment_subgroups';

    public $belongsTo = array(
        'CommentsGroup' => array(
            'className'  => 'CommentsGroup',
            'foreignKey' => false,
            'conditions' => ['`CommentsGroup`.`id` = `CommentsSubGroup`.`group_id`']
        )
    );

}

Comment.php

<?php

App::uses('AppModel', 'Model');

class Comment extends AppModel {

    public $belongsTo = array(
        'CommentsSubGroup' => array(
            'className' => 'CommentsSubGroup',
            'foreignKey' => false,
            'conditions' => ['`CommentsSubGroup`.`id` = `Comment`.`subgroup_id`']
        )
    );

}

When I try from my controller to get the subgroup_id related to the comment it's ok. When I try to get more (the group_id linked to the subgroup_id), I fail.

Query without recursive is ok, otherwise I have:

$data = $this->_Model->find('all', ['conditions' => ['subgroup_id' => $id], 'recursive' => 2, 'order' => [$this->_Modelname . '.id' => 'DESC']]);

Take in consideration $this->_Model equals to Comment .

The error I have:

Database Error Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CommentsSubGroup.group_id' in 'where clause'

SQL Query: SELECT CommentsGroup.id, CommentsGroup.name FROM botobot_comments.comment_groups AS CommentsGroup WHERE CommentsGroup.id = CommentsSubGroup.group_id

Any guess ? Or I am wrong and I should use $hasMany relationship ?

Thanks.

Answer 1

You are correct in using a belongsTo relationship but you need to specify the foreignKey attribute in order for the relationships to work. You also need to take the join condition out of the conditions key (as Cake can figure that out based on the foreign key).

For example:

App::uses('AppModel', 'Model');

class Comment extends AppModel {

    public $belongsTo = array(
        'CommentsSubGroup' => array(
            'className' => 'CommentsSubGroup',
            'foreignKey' => 'subgroup_id'
        )
    );

}

You also need to follow Cakes naming convention with your models and tables otherwise youll need to explicitly declare your table names and primary keys in their respective models.

In your case, CommentsSubGroup should be CommentSubGroup which would assume the table called comment_sub_group and a primary key of comment_sub_group_id