Geoff Garbers

Husband. Programmer. Tinkerer.

Easy way to generate CakePHP HABTM joins for use in pagination.

May 03, 2011

So often I’ve been faced with the task of persisting HABTM joins in CakePHP through pagination database queries.

More often than not, my controllers would end up becoming cluttered by duplicated code building up the joins array to be used in the pagination. Not sure what I’m talking about? Here’s an example:

UsersController::index () {
    $order = 'User.name ASC';
    $fields = array('User.');
    if (isset($this->data['group_id'])) {
        $joins = array();
        $joins[] = array(
            'table' => 'groups_users',
            'foreignKey' => false,
            'conditions' => 'GroupsUser.user_id = User.id',
            'type' => 'INNER',
            'alias' => 'GroupsUser',
        );
        $joins[] = array(
            'table' => 'groups',
            'foreignKey' => false,
            'conditions' => 'GroupsUser.group_id = Group.id',
            'type' => 'INNER',
            'alias' => 'Group',
        );
        $conditions = array('Group.id' => $this->data['group_id']);
        $group = 'User.id';
    }
    $this->paginate = compact('order', 'fields', 'joins', 'conditions', 'group');
    $users = $this->paginate('User');
    $this->set(compact('users'));
}

How do you prevent this?

So what happens if you’ve created this HABTM pagination join in multiple locations in your application, and you make an update to one of the models? Chances are, you’d need to go back to your code, find all the instances of this, and change them to reflect your updates.

Now, I’ve always been a fan of making things easier for myself. That’s why, as soon as I stumbled across this post, I figured I could use it to make my life a bit easier when developing in CakePHP.

So, that’s where I had the bright idea to use this in pagination. Normally, I would have written a custom paginate() method on the model I would be paginating, or I would have manually specified the joins I was looking for in the controller.

So, I decided to shift this pagination logic of the joins out to the model, where I think it belongs. This is the resultant method:

public function generateHabtmJoin ($modelName, $joinType = 'INNER') {
    // If the relation does not exist, return an empty array.
    if (!isset($this->hasAndBelongsToMany[$joinModel])) {
        return array();
    }

    // Init joins, and get HABTM relation.
    $joins = array();
    $assoc = $this->hasAndBelongsToMany[$joinModel];

    // Add the join table.
    $bind = "{$assoc['with']}.{$assoc['foreignKey']} = {$this->alias}.{$this->primaryKey}";
    $joins[] = array(
        'table' => $assoc['joinTable'],
        'alias' => $assoc['with'],
        'type' => $joinType,
        'foreignKey' => false,
        'conditions' => array($bind),
    );

    // Add the next table.
    $bind = "{$joinModel}.{$this->{$joinModel}->primaryKey} = {$assoc['with']}.{$assoc['associationForeignKey']}";
    $joins[] = array(
        'table' => $this->{$joinModel}->table,
        'alias' => $joinModel,
        'type' => $joinType,
        'foreignKey' => false,
        'conditions' => array($bind),
    );

    return $joins;
}

How do you make use of this?

All you do is place this method in your AppModel; and instead of manually forming your $this->paginate['joins'] array in the controller, all you now do is something more like this:

$this->paginate['joins'] = $this->ModelName->generateHabtmJoin('HabtmModelToJoin');

So, let’s use a real-world example. I have a User model, that has and belongs to many Group. In my UsersController index page, I want to be able to filter by groups. Let’s assume I only want to show the users that are in a specific group:

UsersController::index () {
    $order = 'User.name ASC';
    $fields = array('User.');
    if (isset($this->data['group_id'])) {
        $joins = $this->User->generateHabtmJoin('Group', 'INNER');
        $conditions = array('Group.id' => $this->data['group_id']);
        $group = 'User.id';
    }
    $this->paginate = compact('order', 'fields', 'joins', 'conditions', 'group');
    $users = $this->paginate();
    $this->set(compact('users'));
}

And that is a far simpler method of pagination with HABTM models, than having to override the paginate() method every time. Hope this helps someone else!