Why i cant do this query with relations in yii project?
I would like to create a query with Yii relations. This is my relation:
//Articles model
return array(
'articlesHasTags' => array(self::HAS_MANY, 'ArticlesHasTag', 'articles_id',
"with"=>"tag",
),
),
//ArticlesHasTag model
return array(
'articles' => array(self::BELONGS_TO, 'Articles', 'articles_id'),
'tag' => array(self::BELONGS_TO, 'Tag', 'tag_id'),
);
This is the query:
$blog = Articles::model()->with(array("articlesHasTags"))->findAllByAttributes(array(),array(
"condition"=>"t.del = 0 AND t.active = 1 AND articlesHasTags.tag_id = {$tag->id}",
'order'=>"t.publish_start DESC",
"limit"=>10,
));
Before i get the $tag->id .
The error message is:
Unknown column 'articlesHasTags.tag_id'
I think the relation is good because i can use it without this " articlesHasTags.tag_id = {$tag->id} "
I change the relation name to table name. Then the full error message is:
CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'articles_has_tag.tag_id' in 'where clause'. The SQL statement executed was: SELECT t . id AS t0_c0 , t . active AS t0_c1 , t . publish_start AS t0_c2 , t . publish_start_local AS t0_c3 , t . create_time AS t0_c4 , t . last_modify AS t0_c5 , t . title AS t0_c6 , t . url AS t0_c7 , t . short_desc AS t0_c8 , t . content AS t0_c9 , t . image AS t0_c10 , t . comment AS t0_c11 , t . hightlight AS t0_c12 , t . vote AS t0_c13 , t . type AS t0_c14 , t . users_id AS t0_c15 , t . newspaper_id AS t0_c16 , t . del AS t0_c17 , t . region_id AS t0_c18 , t . language_id AS t0_c19 , t . adult AS t0_c20 , users . id AS t1_c0 , users . email AS t1_c1 , users . password AS t1_c2 , users . author_name AS t1_c3 , users . location AS t1_c4 , users . last_login AS t1_c5 , users . active AS t1_c6 , users . remember_me AS t1_c7 , users . rank AS t1_c8 , users . paypal_acc AS t1_c9 , users . url AS t1_c10 , users . about_me AS t1_c11 , users . image AS t1_c12 , users . facebook AS t1_c13 , users . google AS t1_c14 , users . del AS t1_c15 , users . admin_active AS t1_c16 , users . create_time AS t1_c17 , users . first_name AS t1_c18 , users . last_name AS t1_c19 , newspaper . id AS t4_c0 , newspaper . name AS t4_c1 , newspaper . create_time AS t4_c2 , newspaper . type AS t4_c3 , newspaper . active AS t4_c4 , newspaper . url AS t4_c5 , newspaper . location AS t4_c6 , newspaper . newspaper_category_id AS t4_c7 FROM articles t LEFT OUTER JOIN users users ON ( t . users_id = users . id ) LEFT OUTER JOIN newspaper newspaper ON ( t . newspaper_id = newspaper . id ) WHERE (t.del = 0 AND t.active = 1 AND articles_has_tag.tag_id = 42) ORDER BY t.publish_start DESC LIMIT 10
Here is articles_has_tag table
CREATE TABLE IF NOT EXISTS `articles_has_tag` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`articles_id` int(11) DEFAULT NULL,
`tag_id` int(11) DEFAULT NULL,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_articles_has_tag_tag1_idx` (`tag_id`),
KEY `fk_articles_has_tag_articles1_idx` (`articles_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=93 ;
Why you did not use findAll? You put everything in condition. And second thing to mention in your SQL you don`t have relation articlesHasTags.
You can try this.
$blog = Articles::model()
->with("articlesHasTags" => array(
'alias' => 'articlesHasTags',
'together' => true, //join in single query
'condition' => "articlesHasTags.tag_id = {$tag->id}"//may be use param?
))->findAll(array(
"condition"=>"t.del = 0 AND t.active = 1",
'order'=>"t.publish_start DESC",
"limit"=>10,
));
更新：试试这个。
$blog = Articles::model()->with(array('articlesHasTags' => array(
'alias'=>'articleHTag', 'condition' => "articleHTag.tag_id = ".$tag->id)))->findAllByAttributes(array(),array(
"condition"=>"t.del = 0 AND t.active = 1",
'order'=>"t.publish_start DESC",
"limit"=>10,
));