Code for Concinnity

beautiful and elegant solutions


Restoring elegance to CakePHP — doing multiple joins The Right Way™

In my last article about unit testing, I mentioned one way to do ad-hoc multiple joins in CakePHP rather succinctly. Here’s a recap:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
function tagged($tag)
{
    $this->bindModel(array('hasOne'=>array(
        'PostsTag'=>array(
            'foreignKey'=>false,
            'conditions'=>"PostsTag.post_id = Post.id"
        ),

        'Tag'=>array(
            'foreignKey'=>false,
            'conditions'=>"PostsTag.tag_id = Tag.id"
        )
    )));

    return $this->find('all', array('conditions'=>array(
        'Tag.name'=>$tag)));
}

This is, of course, rather unintuitive. A hasOne relationship when in fact I’m trying to look for someone hasAndBelongsToMany? I thought more about it.

There is a Bakery article that talked about doing ad-hoc joins. It looks more technically correct but just too freaking much verbose for my liking:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
    $markers = $this->Marker->find('all', array('joins' => array(
        array(
            'table' => 'markers_tags',
            'alias' => 'MarkersTag',
            'type' => 'inner',
            'foreignKey' => false,
            'conditions'=> array('MarkersTag.marker_id = Marker.id')
        ),
        array(
            'table' => 'tags',
            'alias' => 'Tag',
            'type' => 'inner',
            'foreignKey' => false,
            'conditions'=> array(
                'Tag.id = MarkersTag.tag_id',
                'Tag.tag' => explode(' ', $this->params['url']['q'])
            )
        )
    )));
?>

Actually, a simple refactoring can make it (almost) syntactically sweet and technically more correct:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
// /app/vendors/joins.php

/**
 * A new helper class to produce those join arrays just to make
 * life less miserable
 */
class Joins
{
    public static function left($model, $conditions)
    {
        return self::_makeJoin($model, $conditions, 'left');
    }

    public static function inner($model, $conditions)
    {
        return self::_makeJoin($model, $conditions, 'inner');
    }

    private static function _makeJoin($model, $conditions, $type)
    {
        return array(
            'table'=>Inflector::tableize($model),
            'alias'=>$model,
            'type'=>$type,
            'foreignKey'=>false,
            'conditions'=>$conditions
        );
    }
}

// /app/app_model.php

App::import('Vendor', 'joins');

class AppModel extends Model
{
    // ...
}

// /app/models/post.php

function tagged($tag)
{
    /*
     * Let's make use of our new class, this has become
     * a "one-liner."
     */
    return $this->find('all', array(
        'conditions'=>array('Tag.name'=>$tag),
        'joins'=>array(
            Joins::inner('PostsTag', 'PostsTag.post_id = Post.id'),
            Joins::inner('Tag', "PostsTag.tag_id = Tag.id")
        )
    ));
}
Published by kizzx2, on July 8th, 2010 at 8:30 pm. Filled under: CakePHP Tags: , , , • No Comments