php - Yii Active Record - 关系表上的 WHERE

Question

我的 mysql 数据库中有以下表:

category (id,name, url)
recipe_categories (category_id, recipe_id)
recipes (id, name, description)

我想要实现的SQL语句如下所示:

SELECT * FROM recipes r 
INNER JOIN recipe_categories cr ON cr.recipe_id = r.id
INNER JOIN category c ON c.id = cr.category_id
WHERE c.url = "google.de"

两种模型:

<?php
class RecipeCategoryModel extends CActiveRecord
{
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    public function tableName()
    {
        return 'recipe_categories';
    }


    public function relations()
    {
        return array(
            'recipes'   => array(self::MANY_MANY ,      'RecipeModel',      'recipe_has_categories(recipe_category_id,recipe_id)'),
        );
    }

    public function getActive() {
        return RecipeCategoryModel::model()->with(array(
            'recipes'=>array(
                'joinType'=>'INNER JOIN',
            ),
        ))->findAll();
    }
}

?>


<?php
class RecipeModel extends CActiveRecord
{
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    public function tableName()
    {
        return 'recipes';
    }

    public function relations()
    {
        return array(
            'categories'    => array(self::MANY_MANY ,      'RecipeCategoryModel',      'recipe_has_categories(recipe_id,recipe_category_id)'),
        );
    }

    public function getMainImage() {

    }

}

?>

问题出在 WHERE 子句上。我怎样才能用ActiveRecord(with()，CDbCriteria，...)实现这一点？

Answer 1

在思考这个问题的答案时，我必须稍微清理一下您的数据库表和模型名称，因为提供的代码在测试时导致了一些错误。但我已经能够生成您想要的 SQL 语句 - 但是请记住，当使用 Yii 的 ActiveRecord 时，它使用自己的语法和表别名。

我得到的结果是:

SELECT `t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`, `t`.`description` AS `t0_c2`, `categories`.`id` AS `t1_c0`, `categories`.`name` AS `t1_c1`, `categories`.`url` AS `t1_c2`
FROM `recipe` `t` 
INNER JOIN `recipe2category` `categories_categories` ON (`t`.`id`=`categories_categories`.`recipe_id`) 
INNER JOIN `category` `categories` ON (`categories`.`id`=`categories_categories`.`category_id`) 
WHERE (categories.url = 'google.de')

要在 Yii CActiveRecord 调用中获取 WHERE 语句，您需要在 CDbCriteria 或 FindAll() 参数中添加“条件”或“比较”。请参阅http://www.yiiframework.com/doc/api/1.1/CDbCriteria#condition-detail和 http://www.yiiframework.com/doc/api/1.1/CDbCriteria#addCondition-detail

例如:

$Criteria = new CDbCriteria;
$Criteria->with = array(
'categories' => array(
    'joinType' => 'INNER JOIN'
    ),
);
$Criteria->condition = 'categories.url = :url';
$Criteria->params = array(
    ':url' => 'google.de',
);

$Recipes = Recipe::model()->findAll($Criteria);

但是要使该代码正常工作，您需要清理数据库表和模型:

TABLE recipe - id, name, description
TABLE recipe2category - category_id, recipe_id
TABLE category - id, name, url

模型Recipe.php:

<?php
class Recipe extends CActiveRecord
{
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    public function tableName()
    {
        return 'recipe';
    }

    public function relations()
    {
        return array(
            'categories'    => array(self::MANY_MANY , 'RecipeCategory', 'recipe2category(recipe_id,category_id)'),
        );
    }
}

?>

模型RecipeCategory.php:

<?php
class RecipeCategory extends CActiveRecord
{
    public static function model($className=__CLASS__)
    {
        return parent::model($className);
    }

    public function tableName()
    {
        return 'category';
    }

    public function relations()
    {
        return array(
        'recipes'   => array(self::MANY_MANY , 'Recipe', 'recipe2categoy(category_id,recipe_id)'),
        );
    }
}

?>

希望这有用。