CakePHP how to retrieve HABTM data with conditions?

Question

I use CakePHP 2.2.2 I have 3 tables: restaurants, kitchens and kitchens_restaurants - join table for HABTM.

In Restaurant model I have:

public $hasAndBelongsToMany = array(
    'Kitchen' =>
        array(
            'className'              => 'Kitchen',
            'joinTable'              => 'kitchens_restaurants',
            'foreignKey'             => 'restaurant_id',
            'associationForeignKey'  => 'kitchen_id',
            'unique'                 => true,
            'conditions'             => '',
            'fields'                 => 'kitchen',
            'order'                  => '',
            'limit'                  => '',
            'offset'                 => '',
        ),

The problem is that I have separate controller for my main page in which I need to retrieve data from this models with complex conditions.

I added

public $uses = array('Restaurant');

to my main page controller and here comes the part where I need your advices.

I need to select only those restaurants where kitchen = $id. I've tried to add

public function index() {   
$this->set('rests', $this->Restaurant->find('all', array(
'conditions' => array('Restaurant.active' => "1", 'Kitchen.id' => "1")
)));

}

and I got SQLSTATE[42S22]: Column not found: 1054 Unknown column in 'where clause' error. Obviously I need to fetch data from "HABTM join table" but I don't know how.

Answer 1

TLDR:

To retrieve data that's limited based on conditions against a [ HABTM ]'s association, you need to use [ Joins ].

Explanation:

The code below follows the [ Fat Model/Skinny Controller ] mantra, so the logic is mostly all in the model, and just gets called from a controller.

Note: You don't need all those HABTM parameters if you follow the [ CakePHP conventions ] (which it appears you are).

The below code has not been tested (I wrote it on this site), but it should be pretty darn close and at least get you in the right direction.

Code:

//Restaurant model

public $hasAndBelongsToMany = array('Kitchen');

/**
 * Returns an array of restaurants based on a kitchen id
 * @param string $kitchenId - the id of a kitchen
 * @return array of restaurants
 */
public function getRestaurantsByKitchenId($kitchenId = null) {
    if(empty($kitchenId)) return false;
    $restaurants = $this->find('all', array(
        'joins' => array(
             array('table' => 'kitchens_restaurants',
                'alias' => 'KitchensRestaurant',
                'type' => 'INNER',
                'conditions' => array(
                    'KitchensRestaurant.kitchen_id' => $kitchenId,
                    'KitchensRestaurant.restaurant_id = Restaurant.id'
                )
            )
        ),
        'group' => 'Restaurant.id'
    ));
    return $restaurants;
}

//Any Controller

public function whateverAction($kitchenId) {
    $this->loadModel('Restaurant'); //don't need this line if in RestaurantsController
    $restaurants = $this->Restaurant->getRestaurantsByKitchenId($kitchenId);
    $this->set('restaurants', $restaurants);
}

Answer 2

There is a much cleaner way than the solution provided by Dave.

First you need to set a reverse HABTM Relationship between Restaurant and Kitchen in the Kitchen Model.

Than you just make a find for the Kitchen you are interested in (id = 1) and you will get the associated restaurants, using Containable Behavior for filtering by Restaurant fields.

$this->Restaurant->Kitchen->Behaviors->attach('containable'); // Enable Containable for Kitchen Model

$this->Restaurant->Kitchen->find('first', array(
    'recursive' => -1 // don't collect other data associated to Kitchen for performance purpose
    'conditions' => array('Kitchen.id' => 1),
    'contain' => array('Restaurant.active = 1')
));

Source