我的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, urlModel 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, urlModel 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.
更多推荐
发布评论