Yii Active Record

Yii Active Record - 关系表上的WHERE(Yii Active Record - WHERE on relations-table) mysql

我的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-clausel。 我如何通过ActiveRecord（with（），CDbCriteria，...）实现这一点？

I have the following tables in my mysql database:

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

The SQL statement I want to realize looks like that:

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"

The two models:

<?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() { } } ?>

The problem is the WHERE-clausel. How can I realize this with ActiveRecord (with(), CDbCriteria,...)?

最满意答案

在考虑这个问题的答案时，我不得不清理你的db表和模型名称，因为提供的代码在测试时会导致一些错误。 但是我已经能够生成你想要的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

Model 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)'), ); } } ?>

Model 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)'), ); } } ?>

希望这很有用。

While thinking about the answer for this I had to clean up your db table and Model names a little, as the code provided caused some errors while testing. But I have been able to generate the SQL statement you want - however remember when using Yii's ActiveRecord it uses its own syntax and table aliases.

The result I have is:

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')

To get a WHERE statement in a Yii CActiveRecord call you need to add a 'condition' or 'compare' into your CDbCriteria or FindAll() params. See http://www.yiiframework.com/doc/api/1.1/CDbCriteria#condition-detail and http://www.yiiframework.com/doc/api/1.1/CDbCriteria#addCondition-detail

For example:

$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);

But for that code to work you'll need my cleaned up db tables and models:

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

Model 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)'), ); } } ?>

Model 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)'), ); } } ?>

Hope that's useful.