CakePHP - HABTM 分页查询的问题

问题描述

表格

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 分页查询的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！