CakePHP - problem with HABTM paginate query

Question

Tables

restaurants
cuisines
cuisines_restaurants

Both restaurant and cuisine model are set up to HABTM each other.

I'm trying to get a paginated list of restaurants where Cuisine.name = 'italian' (example), but keep getting this error:

1054: Unknown column 'Cuisine.name' in 'where clause'

Actual query it's building:

SELECT `Restaurant`.`id`, `Restaurant`.`type` ..... 
`Restaurant`.`modified`, `Restaurant`.`user_id`, `User`.`display_name`,
`User`.`username`, `User`.`id`, `City`.`id`,`City`.`lat`  ..... 
FROM `restaurants` AS `Restaurant` LEFT JOIN `users` AS `User` ON 
(`Restaurant`.`user_id` = `User`.`id`) LEFT JOIN `cities` AS `City` ON 
(`Restaurant`.`city_id` = `City`.`id`) WHERE `Cuisine`.`name` = 'italian' 
LIMIT 10

The "....." parts are just additional fields I removed to shorten the query to show you.

I'm no CakePHP pro, so hopefully there's some glaring error. I'm calling the paginate like this:

$this->paginate = array(
    'conditions' => $opts,
    'limit' => 10,
);
$data = $this->paginate('Restaurant');
$this->set('data', $data);

$opts is an array of options, one of which is 'Cuisine.name' => 'italian'

I also tried setting $this->Restaurant->recursive = 2; but that didn't seem to do anything (and I assume I shouldn't have to do that?)

Any help or direction is greatly appreciated.

---

EDIT

models/cuisine.php
    var $hasAndBelongsToMany = array('Restaurant');

models/restaurant.php
    var $hasAndBelongsToMany = array(
    'Cuisine' => array(
        'order' => 'Cuisine.name ASC'
    ),
    'Feature' => array(
        'order' => 'Feature.name ASC'
    ),
    'Event' => array(
        'order' => 'Event.start_date ASC'
    )
);

Answer 1

This fails because Cake is actually using 2 different queries to generate your result set. As you've noticed, the first query doesn't even contain a reference to Cuisine.

As @vindia explained here, using the Containable behavior will usually fix this problem, but it doesn't work with Paginate.

Basically, you need a way to force Cake to look at Cuisine during the first query. This is not the way the framework usually does things, so it does, unfortunately, require constructing the join manually . paginate takes the same options as Model->find('all'). Here, we need to use the joins option.

var $joins = array(
    array(
        'table' => '(SELECT cuisines.id, cuisines.name, cuisines_restaurants.restaurant_id
                 FROM cuisines_restaurants 
                 JOIN cuisines ON cuisines_restaurants.cuisines_id = cuisines.id)',
        'alias' => 'Cuisine',
        'conditions' => array(
            'Cuisine.restaurant_id = Restaurant.id',
            'Cuisine.name = "italian"'
        )
    )
);

$this->paginate = array(
    'conditions' => $opts,
    'limit' => 10,
    'joins' => $joins
);

This solution is a lot clunkier than the others, but has the advantage of working.

Answer 2

As explained in this blogpost by me you have to put the condition of the related model in the contain option of your pagination array.

So something like this should work

# in your restaurant_controller.php
var $paginate = array(
    'contain' => array(
        'Cuisine' => array(
            'conditions' => array('Cuisine.name' => 'italian')
        )
    ),
    'limit' => 10
);

# then, in your method (ie. index.php)
$this->set('restaurants', $this->paginate('Restaurant'));