查找使用contain with条件

Question

您好，我有以下模型

Restaurant(id, name)

RestaurantMenu(id, name, restaurant_id)每家餐厅都有许多主菜单

RestaurantMenuItem(id, name, restaurant_menu_id)每个菜单都有许多菜单项

我需要找到那些至少有1个RestaurantMenuItem的RestaurantMenu。如果它没有，那么它也不应该获得RestaurantMenu。它给了我空数组RestaurantMenuItem，这是我不想要的。我不想要一个空对象。

$this->Behaviors->attach('Containable');

return $this->find('all', array(
    'contain' => array('RestaurantMenu.RestaurantMenuItem'),
    'conditions' => array(
        'Restaurant.id' => $restaurant_id,
    ),
    'recursive' => 0
));

Answer 1

这可以通过在RestaurantMenu容器上应用INNER join和RestaurantMenuItem来实现。这将导致仅当存在至少一个关联的RestaurantMenuItem行时才选择RestaurantMenu，这应该同时满足您的两个条件。

不幸的是，对于容器没有joins和group选项，所以必须通过例如手动查询 RestaurantMenu data 来解决这个问题，这在您的情况下应该非常简单，因为您只选择了一个Restaurant (顺便说一句，在这种情况下，您可能应该使用first finder而不是all )：

$restaurant = $this->find('first', array(
    'conditions' => array(
        'Restaurant.id' => $restaurant_id,
    ),
    'recursive' => 0
));

if ($restaurant) {
    // RestaurantMenu needs to have the containable behavior attached
    // in order for this to work
    $restaurant['RestaurantMenu'] = $this->RestaurantMenu->find('all', array(
        'contain' => array(
            'RestaurantMenuItem'
        ),
        'joins' => array(
            array(
                'table' => 'restaurant_menu_item',
                'alias' => 'RestaurantMenuItem',
                'type' => 'INNER',
                'conditions' => array(
                    'RestaurantMenu.id = RestaurantMenuItem.restaurant_menu_id'
                )
            )
        ),
        'conditions' => array(
            'RestaurantMenu.restaurant_id' => $restaurant_id,
        ),
        'group' => array(
            'RestaurantMenu.id'
        )
    ));
}

return $restaurant;

或通过使用 finderQuery 选项的来定义包含联接的自定义查询：

return $this->find('all', array(
    'contain' => array(
        'RestaurantMenu' => array(
            'finderQuery' => '
                SELECT
                  RestaurantMenu.*
                FROM
                    restaurant_menus AS RestaurantMenu
                INNER JOIN
                  restaurant_menu_items AS RestaurantMenuItem ON
                    RestaurantMenuItem.restaurant_menu_id = RestaurantMenu.id
                WHERE
                  RestaurantMenu.restaurant_id IN ({$__cakeID__$})
                GROUP BY
                  RestaurantMenu.id
            ',
            'RestaurantMenuItem'
        )
    ),
    'conditions' => array(
        'Restaurant.id' => $restaurant_id,
    )
));

这也可以应用于Restaurant模型中的RestaurantMenu关联配置。

另请参阅