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.

Solution 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);
}

Solution 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

Solution 3

You can not need use [join], because use have setting [ HABTM ]'s association
Kitchen model hasAndBelongsToMany Restaurant model so that you can code as bellow

KitchensControllers
<?php
  public function index() {
    $this->Kitchen->recursive = 0;
    $kitchens = $this->Kitchen->find('all', array('contain' => array('Restaurant')));
    $this->set('kitchens', $kitchens);
  }
?>

Good luck!

Comments

[user1327]

Dave, thank you. Your code works very well. One little question – is it possible to select only fields that I want (e.g. I only need company name, and some other info, not all fields)? I've tried to add 'fields' => array('Restaurant.companyname') to the function getRestaurantsByKitchenId (in Restaurants model) but it doesn't cause any effect.

[Dave]

@user1327 - Glad to help. Yes, you should be able to add 'fields'=>array() to the Model method. Just put it at the same level as 'joins' (not within). You can always use DebugKit to easily see what queries are being run, and can tweak based on that.

[user1327]

Oh, silly me. Now everything works as I want it to. Once again – thank you.

[thecodedeveloper.com]

thanks for great answer, you saved my lot of time thanks again

[tsukimi]

wont this return all the kitchens?

[Joseph]

Yes, I think that this would return all of the kitchens. However, the code is correct for finding all of the records in Model A and showing the related HABTM records from Model B.

[Sven Mäurer]

Is it possible to use the method in combination with pagination?

[Dave]

@SvenMäurer, yes, see this answer for how to keep paginate in your model: stackoverflow.com/questions/6501849/…

[Quy Le]

yes, it's return all kitchens, if you want to find with conditions you can use as bellow sample: $this->Kitchen->find('all', array('conditions' => array('Kitchen.id >' => '5'), 'contain' => array('Restaurant')))

[Jonathan]

What shall this represent? "$this->Restaurant->Kitchen->"