CakePHP: retrieve an association from a HABTM join table

0
votes

In CakePHP 2.0, we have a Subscriber model and a SubscriberGroup model. They are both linked HABTM to each other.

In our SubscriberGroup edit action, we want to get a list of subscribers in this group, which is not a problem when the HABTM relationship is defined. However, the association table, subscriber_groups_subscribers, also has an author_id field, which is a foreign key to another table - users.

So when we do $this->SubscriberGroups->read(), we want to get not only the group and the subscribers, but also the details from the users table regarding the user who created the connection.

The HABTM relationship is defined as follows:

public $hasAndBelongsToMany = array(
    'Subscriber' => array(
         'className' => 'Subscriber',
         'joinTable' => 'subscriber_groups_subscribers',
         'foreignKey' => 'subscriber_group_id',
         'associationForeignKey' => 'subscriber_id',
         'fields' => 'id,first_name,last_name,email_address,last_error',
         'unique' => 'keepExisting'
    )
);

Is there a way to do this without defining a custom query?

2
cakephp
Do you have a belongsTo (or hasOne) relationship between Subscriber and Author? If so, try to set $this->SubscriberGroup->recursive to 3. – Holt
Holt - I'm looking for the author of the relationship, not the subscriber. – user884248
Sorry, skipped a line in your question... I'm not sure CakePHP has something "clean" for HABTM relationship with custom field. I would create a model for the HABTM table and add belongsTo and hasOne relationship between this model and Author, Subscriber and SubscriberGroup models. – Holt

2 Answers

0
votes

Well, to achieve this you probably may want to use containable behavior:

$options = array(
    'contain' => array(
        'Subscriber', 'Author'
    ) 
);

$data = $this->SubscriberGroup->find('all', $options);

And don't forget to set containable behavior for SubscriberGroup model!

class SubscriberGroup extends AppModel {
    public $actsAs = array('Containable');
}
0
votes

I think there is no way to make a HABTM relationship with 3 tables using CakePHP, even a HABTM with one extra field is complicated.

The easiest way is to create a "Link" model and add hasOne relationship on your SubscriberGroup model:

class LinkModel extends AppModel {

    public $useTable  = 'subscriber_groups_subscribers' ;

    public $actAs = array('Containable') ;

    public $belongsTo = array(
        'Subscriber',
        'Author',
        'SubscriberGroup'
    ) ;

}

class SubscriberGroup extends AppModel {
    public $actAs = array('Containable') ;

    public $hasMany = array(
        'LinkModel'
    ) ;
}

Then, here is what you get with a call to read:

$this->SubscriberModel->recursive = 2 ;
$res = $this->SubscriberModel->read(null, 1) ;
debug($res) ;

Output:

array(
    'SubscriberGroup' => array(
        'id' => '1'
    ),
    'LinkModel' => array(
        (int) 0 => array(
            'id' => '2',
            'author_id' => '1',
            'subscriber_id' => '3',
            'subscriber_group_id' => '1',
            'Subscriber' => array(
                'id' => '3'
            ),
            'Author' => array(
                'id' => '1'
            ),
            'SubscriberGroup' => array(
                'id' => '1'
            )
        ),
        (int) 1 => array(
            'id' => '3',
            'author_id' => '1',
            'subscriber_id' => '1',
            'subscriber_group_id' => '1',
            'Subscriber' => array(
                'id' => '1'
            ),
            'Author' => array(
                'id' => '1'
            ),
            'SubscriberGroup' => array(
                'id' => '1'
            )
        ),
        (int) 2 => array(
            'id' => '4',
            'author_id' => '2',
            'subscriber_id' => '1',
            'subscriber_group_id' => '1',
            'Subscriber' => array(
                'id' => '1'
            ),
            'Author' => array(
                'id' => '2'
            ),
            'SubscriberGroup' => array(
                'id' => '1'
            )
        )
    )
)