CakePHP - HABTM分页查询的问题 [英] CakePHP - problem with HABTM paginate query

问题描述

表

 餐厅
菜肴
 cuisines_restaurants 
  
 
 
 
餐厅和美食模式都设置为HABTM。
 
 
 
得到一个分页的菜单，其中Cuisine.name ='意大利语（例子），但不断得到这个错误：
  1054 ：'where clause'中的未知列'Cuisine.name'
  
正在构建的实际查询：
  SELECT`Restaurant`.````````````````type` $`
````。 `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 
  
.....部分只是为了缩短查询而删除的其他字段
 
 
 
我没有CakePHP pro，所以希望有一些明显的错误。我这样调用分页：
  $ this-> paginate = array（
'conditions' > $ opts，
'limit'=> 10，
）; 
 $ data = $ this-> paginate（'Restaurant'）; 
 $ this-> set（'data'，$ data）; 
  
 $ opts是一个选项数组，其中之一是'Cuisine.name'=>'意大利语'
 
 
 
我也试过设置$ this-> Restaurant-> recursive = 2;但这似乎没有做任何事情（我认为我不应该这样做？）
 
 
 
任何帮助或方向是非常感谢。
 
 
 
 
 
 
  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'
）
）; 
  
 
 
解决方案
此操作失败，因为Cake实际上使用2个不同的查询生成结果集。正如你所注意到的，第一个查询甚至不包含对Cuisine的引用。
 
 
 
由于@vindia解释了此处，使用可容纳行为通常会 解决此问题，但不能与Paginate 一起使用。 
 
 
 
基本上，你需要一种方法强迫Cake在第一次查询期间查看Cuisine。这不是框架通常做的事情，所以它不幸地需要手动构造连接 
。  paginate 使用与 Model-> find（'all'）相同的选项。这里，我们需要使用 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 
）; 
  
这个解决方案比其他解决方案更复杂，但是具有工作的优点。
 
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'
    )
);

解决方案
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.

                        
这篇关于CakePHP  -  HABTM分页查询的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！