Here’s a problem that had me scratching my head for hours.

Creating views with HABTM relationships should be a bread-and-butter task in any web application. So you’d think Cakephp has made the workflow as intuitive as possible, but what a headache.

The standard method of HABTM queries in the documentation seems intuitive enough at first glance. And this is the approach I’ve used for a while.

    $this->Recipe->bindModel(array('hasOne' => array('RecipesTag')));
    $this->Recipe->find('all', array(
    'fields' => array('Recipe.*'),
    'conditions'=>array('RecipesTag.tag_id'=>124) // id of Dessert
    ));

That is until I needed to be able to sort the resulting table by the deeper associations with pagination. In which case something like:

    $paginator->sort('Condition','Disorder.condition_id');

Where Disorder is a deep associated table two levels down, is going to return the error that the column Disorder.condition cannot be found.

The problem is that the table disorders is not part of FROM in the statement generated by Cakephp. Forcing a $Model->recursive = 2 does nothing.

The standard solution is elusive enough that a search on Stackoverflow shows people repeating the same incorrect recommendations over and over again. The most elegant solution would’ve been to use Containable, but its “conditions” filter returns tuplets with empty results. Apperantly it’s a deliberate design decision. We are supposed to filter out the empty tuplets ourselves. This is all fine and dandy, except that will cause problem with the pagination page counter!

The recommendation is to use on-the-fly associations. But honestly, why should I relearn a completely different set of of abstraction just to do something I can do so much faster with regular SQL queries?

The solution I came up with is to manually state my own joins. Not very flashy, but it gets the job done, and $paginator->sort() works the way one would expect.

Here’s some code:

     $this->Project->Patient->recursive = -1;

        $this->paginate['Patient']['joins'] = array(
                array(
                    'table' => 'patients_projects',
                    'alias' => 'PatientsProject',
                    'type' => 'inner',
                    'conditions' => array('Patient.id = PatientsProject.patient_id')
                ),
                array(
                    'table' => 'projects',
                    'alias' => 'Project',
                    'type' => 'inner',
                    'conditions' => array('PatientsProject.project_id = Project.id')
                ),
                array(
                    'table' => 'disorders',
                    'alias' => 'Disorder',
                    'type' => 'left outer',
                    'conditions' => array('Patient.id = Disorder.patient_id')
                ),
                array(
                    'table' => 'conditions',
                    'alias' => 'Condition',
                    'type' => 'left outer',
                    'conditions' => array('Condition.id = Disorder.condition_id')
                )
            );
        $options = array('PatientsProject.project_id' => $id);
        $this->set('patients', $this->paginate('Patient', $options));

And in the view, you can sort as thus:

    sort('firstName');
    sort('lastName');
    sort('gender');
    sort('age','birthday');
    sort('Condition','Disorder.condition_id');
    sort('hemisphere','Disorder.hemisphere');