Joins en CakePHP: buscar con registros relacionados

Voy a intentar escribir varias entradas sobre el uso de JOIN con CakePHP, porque me parece un tema interesante, demandado, y relativamente poco conocido en el framework.

Que es eso de JOIN

En SQL la cláusula JOIN nos permite hacer búsquedas en varias tablas de la base de datos que estén relacionadas entre sí.

Así, por ejemplo, si tenemos el típico caso de Post hasAndBelongsToMany Tag, usando JOINS podemos hacer una búsqueda de aquellos Post que hayan sido etiquetados con una Tag determinada.

Para entender el concepto de JOIN puedes imaginarte que tomas cada registro de la tabla "principal" y le añades los campos del registro relacionado en otra tabla, quedándote una especie de "supertabla", y en ella puedes realizar búsquedas sobre cualquier campo de ambas tablas.

Existen varios tipos de JOIN: LEFT, RIGHT e INNER, de los que hablaré en otra entrada, que nos permiten definir cómo queremos que se combinen los datos de las tablas. El resultado puede ser muy diferente en cada caso y el tipo elegido depende de nuestras finalidades.

La situación en CakePHP

Como bien sabes, CakePHP utiliza una sintaxis especial basada en arrays para crear las querys a la base de datos. La ventaja de este sistema es que nos permite cambiar con relativa facilidad del motor de base de datos. Este "lenguaje super-SQL" aparece documentado en el manual, pero no menciona los joins.

Por una parte, algunos tipos de asociaciones de Modelos de CakePHP ejecutan finds creando los JOIN necesarios y es posible hacer búsquedas de un modelo, usando campos de modelos relaciones, pero no es el caso de nuestras queridas hasAndBelongsToMany (creo que las hasMany tampoco).

Otro aspecto importante tiene que ver con la paginación, pues también hay complicaciones cuando se intenta paginar con relaciones habtm.

Hay algunos trucos para hacer estas búsquedas en relaciones habtm (como redefinir las asociaciones según convenga como hasMany y belongsTo de forma temporal, o directamente escribir el query a mano y ejecutarlo con Model->query()), pero este artículo de Nate Abele en Bakery sobre el uso de joins me abrió los ojos a una solución que para mí es mucho mejor: cómo forzar joins en CakePHP.

Joins en CakePHP, la respuesta corta

1. Para hacer joins hay que usar el método Model->find($type, $options), donde $type es el tipo de find ('all', 'list', 'first', o uno personalizado) y $options es nuestro array con todos los detalles del query.
2. Una de las claves de $options será justamente 'joins'
3. Cada 'join' se representa mediante un array que tiene estas claves
1. 'table': el nombre de la tabla
2. 'alias': normalmente el nombre del modelo asociado a la tabla
3. 'type': el tipo de join
4. 'conditions': el array de condiciones que nos dice cómo se relacionan las tablas (básicamente las condiciones que pondríamos en la cláusula ON)
5. 'foreignKey': es un boolean, aunque no tengo claro cuál es su función y en los ejemplos que he visto se pone a false (echando un vistazo a dbo_source me pregunto si ponerlo en true serviría para hacer algo de automagia con las asociaciones ya existentes, pero no lo tengo nada claro).
4. Pones todas las definiciones de joins que necesites.
5. Para hacer la búsqueda, añades condiciones en la clave 'conditions' como es habitual.

Un ejemplo

Lo siguiente es un fragmento del código que estoy usando en un método find personalizado de un proyecto en el que estoy trabajando. Espero que se entienda.

Se trata de un gestor de contenidos, en el que tengo los modelos Channel, Item y User de tal modo que Channel hasMany Item, y Channel hasAndBelongsToMany User.  Es decir, un Channel puede tener varios Item y cada Channel está asociado a varios Usuarios (que pueden estar asociados a varios Channel).

Ocurre que algunos Channel son "privados" para los User que están asociados a ellos. Quería un método find capaz de devolverme los Items de los Channel privados a los que un User tiene acceso. Para ello hago los siguientes joins

Item JOIN Channel para poder buscar los Channel marcados como privados (ver Channel.private en la clave 'conditions').

Channel JOIN ChannelsUser para poder restringir la búsqueda a los Channel asociados con el User (en este caso conozco el id del usuario, dato que paso a través de una clave propia 'user' y que puedo encontrar en la tabla channels_users, pero podría hacer un JOIN a user para restringir la búsqueda por algún otro campo de User). ChannelsUser es el joinModel de la asociación Channel habtm User, aunque no es necesario que lo definas expresamente (por supuesto, sí que necesitas tener la tabla channels_users par que funcione la relación).

/* Aquí definimos los join */

$extra['joins'] = array(

    array('table' => 'channels',

        'alias' => 'Channel',

        'type' => 'LEFT',

        'foreignKey' => FALSE,

        'conditions' => array(

            'Channel.id = Item.channel_id',

        )

    ),

    array('table' => 'channels_users',

        'alias' => 'ChannelsUser',

        'type' => 'LEFT',

        'foreignKey' => FALSE,

        'conditions' => array(

            'ChannelsUser.channel_id = Channel.id',

            'ChannelsUser.user_id' => $query['user']

        )

    )

);

/* Aquí están las condiciones, como puedes ver, ya podemos usar campos de los modelos relacionados, en este caso Channel */

$extra['conditions'] = array(

    'Channel.private' => 1,

    'Item.publish' => 1,

    'Item.pubDate <= curdate()',

    'or' => array(

        'Item.expiration is null',

        'Item.expiration > curdate()',

    )

);

$extra['order'] = array(

'Item.pubDate' => 'desc',

'Item.created' => 'desc'

);

/* Finalmente mezclo las opciones definidas aquí, con las que se hayan pasado como argumento a find */

$query = Set::merge($query, $extra);

Algunos detalles

Fíjate que los arrays que definen los joins no llevan ninguna clave, en todo caso podría ser numérica para definir el orden en que deben unirse las tablas (aunque lo defines simplemente escribiéndolas en el orden adecuado).

Otro detalle importante es la forma en que defino las 'conditions' dentro del join, en lugar de utilizar el formato típico de 'Modelo.campo' => 'OtroModelo.campo', lo hago 'Modelo.campo => OtroModelo.campo'. CakePHP parece asumir que el valor a la derecha siempre es un valor, no un campo (le pone comillas de valor, no de campo) y la condición nunca se cumple.

Si en lugar de un campo es un valor, el join funciona bien.