問題描述

表格

restaurants
cuisines
cuisines_restaurants

餐廳和美食模型都相互設置為 HABTM.

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

我正在嘗試獲取分頁的餐廳列表，其中 Cuisine.name = 'italian'(示例)，但不斷收到此錯誤:

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'

正在構建的實際查詢:

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.

我不是 CakePHP 專家，所以希望有一些明顯的錯誤.我像這樣調用分頁:

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 是一組選項，其中一個是 'Cuisine.name' => 'italian'

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

我也嘗試設置 $this->Restaurant->recursive = 2;但這似乎沒有任何作用(我認為我不應該這樣做?)

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.

編輯

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'
    )
);

推薦答案

這失敗了，因為 Cake 實際上使用了 2 個不同的查詢來生成結果集.正如您所注意到的，第一個查詢甚至不包含對 Cuisine 的引用.

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.

正如@vindia 在此處所解釋的那樣，使用 Containable 行為將通常解決這個問題，但是它不適用于 Paginate.

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

基本上，您需要一種方法來強制 Cake 在第一次查詢期間查看 Cuisine.這不是框架通常的工作方式，因此不幸的是，它確實需要 手動構建連接.paginate 采用與 Model->find('all') 相同的選項.在這里，我們需要使用 joins 選項.

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.

這篇關于CakePHP - HABTM 分頁查詢的問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！