我的 CakePHP HABTM 查询出了什么问题?

发布于 2024-10-06 05:49:21 字数 830 浏览 3 评论 0原文

我有一个名为“地标”的表和一个名为“类别”的表,彼此之间具有 HABTM 关系。我正在尝试使用以下代码检索特定地标的所有类别:

$this->set('selected_categories', $this->Category->find('list',
    array('contain'=>array(
        'Landmarks'=>array(
           'conditions'=>array('Landmark.num'=>7)
)))));

我已经测试了由此产生的数据库查询(如调试级别 2 中打印的那样),并且它似乎获得了正确的结果,即一个子集的类别。但是,当我在视图中 var_dump $selected_categories 时,我得到的是所有类别的列表。

我在这里遗漏了一些明显的东西吗?

ETA:当我说上述查询有效时,我撒了谎。页面上的其他内容正在生成我想要的 SQL 查询!这是:

SELECT `Categories`.`num`, `CategoriesLandmark`.`category_num`,
  `CategoriesLandmark`.`landmark_num` 
FROM `categories` AS `Categories` JOIN `categories_landmarks` 
AS `CategoriesLandmark` ON (`CategoriesLandmark`.`landmark_num` = 7
AND `CategoriesLandmark`.`category_num` = `Categories`.`num`) WHERE 1 = 1

I have a table called Landmarks and a table called Categories with a HABTM relationship to each other. I am trying to retrieve all the categories for a specific Landmark with the following code:

$this->set('selected_categories', $this->Category->find('list',
    array('contain'=>array(
        'Landmarks'=>array(
           'conditions'=>array('Landmark.num'=>7)
)))));

I've tested the database query that results from this (as printed out in debug level 2), and it seems to get the right results, i.e. a subset of the Categories. However, when I var_dump $selected_categories in the view, I'm getting a list of ALL categories instead.

Am I missing something obvious here?

ETA: I told a lie when I said the above query was working. Something else on the page is generating the SQL query I want! Which is:

SELECT `Categories`.`num`, `CategoriesLandmark`.`category_num`,
  `CategoriesLandmark`.`landmark_num` 
FROM `categories` AS `Categories` JOIN `categories_landmarks` 
AS `CategoriesLandmark` ON (`CategoriesLandmark`.`landmark_num` = 7
AND `CategoriesLandmark`.`category_num` = `Categories`.`num`) WHERE 1 = 1

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

遗心遗梦遗幸福 2024-10-13 05:49:21

将查找类型指定为“列表”与 Containable 行为不兼容。

使用 joins 代替来完成此操作:

$selectedCategories = $this->Category->find('list', array(
    'joins' => array(
        array(
            'table' => 'categories_landmarks',
            'alias' => 'CategoriesLandmark',
            'type' => 'inner',
            'conditions' => array("CategoriesLandmark.category_id = Category.id")
        ),
        array(
            'table' => 'landmarks',
            'alias' => 'Landmark',
            'type' => 'inner',
            'conditions' => array(
                "CategoriesLandmark.landmark_id = Landmark.id",
                'Landmark.num' => 7
            )
        ),
    )
));

理想情况下,我会将其直接放入类别模型中,如下所示:

function fetchListByLandmarkNum($landmarkNum) {
    return $this->find('list', array(
        'joins' => ...
    ));
}

然后从控制器调用它:

$selectedCategories = $this->Category->fetchListByLandmarkNum(7);
$this->set(compact('selectedCategories'));

始终使模型变胖,控制器变瘦。您现在可以在其他地方重用此函数。 :)


返回所有类别的原因是该条件应用于相关的 Landmark 模型。此 Containable 查询的结果将是获取所有类别,并仅返回满足 num = 7 的那些 Landmark 模型。不应将其解释为仅返回 Landmark 模型满足条件的那些类别。

Specifying the find type as 'list' is incompatible with the Containable behavior.

Use joins instead to accomplish this:

$selectedCategories = $this->Category->find('list', array(
    'joins' => array(
        array(
            'table' => 'categories_landmarks',
            'alias' => 'CategoriesLandmark',
            'type' => 'inner',
            'conditions' => array("CategoriesLandmark.category_id = Category.id")
        ),
        array(
            'table' => 'landmarks',
            'alias' => 'Landmark',
            'type' => 'inner',
            'conditions' => array(
                "CategoriesLandmark.landmark_id = Landmark.id",
                'Landmark.num' => 7
            )
        ),
    )
));

I would ideally place this directly into the Category model like so:

function fetchListByLandmarkNum($landmarkNum) {
    return $this->find('list', array(
        'joins' => ...
    ));
}

And then call it from the controller:

$selectedCategories = $this->Category->fetchListByLandmarkNum(7);
$this->set(compact('selectedCategories'));

Always make your models fat and your controllers skinny. You can now reuse this function elsewhere. :)


The reason why ALL the categories are being returned is that the condition is applied on the related Landmark models. The result of this Containable query would be to fetch all the Categories, and return only those Landmark models which satisfy num = 7. It should NOT be interpreted as returning only those Categories for which the Landmark models satisfy the condition.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文