我试图让这个mysql查询与Yii模型一起使用,但我不能。
SELECT COUNT( qhc.countries_id) AS counter, q.question, co.name
FROM questions AS q , countries as co, questions_has_countries AS qhc
WHERE qhc.questions_id = q.id
AND co.id = qhc.countries_id
GROUP BY question
HAVING counter = 2
到目前为止,我有这个,但不知何故,你似乎没问题,它不起作用:
$criteria = new CDbCriteria();
$criteria->select = 'question, COUNT(countries_id) as counter';
$criteria->with = array('countries', 'categories');
$criteria->addInCondition('countries.id' , $_POST['Questions']['countries']);
$criteria->group = 'question';
$criteria->having = ('counter = 1');
$model = Questions::model()->findAll($criteria)
请帮忙,我对 Yii 框架很陌生。
谢谢。
日志中的 sql :
SELECT `t`.`question` AS `t0_c1`,
COUNT(countries_id) as counter, `t`.`id` AS `t0_c0`, `countries`.`id` AS
`t1_c0`, `countries`.`name` AS `t1_c1`, `categories`.`id` AS `t2_c0`,
`categories`.`name` AS `t2_c1` FROM `questions` `t` LEFT OUTER JOIN
`questions_has_countries` `countries_countries` ON
(`t`.`id`=`countries_countries`.`questions_id`) LEFT OUTER JOIN `countries`
`countries` ON (`countries`.`id`=`countries_countries`.`countries_id`)
LEFT OUTER JOIN `questions_has_categories` `categories_categories` ON
(`t`.`id`=`categories_categories`.`questions_id`) LEFT OUTER JOIN
`categories` `categories` ON
(`categories`.`id`=`categories_categories`.`categories_id`) WHERE
(countries.id=:ycp0) GROUP BY question HAVING (counter = 2). Bound with
:ycp0='1'
你已经完成了大部分工作。现在,您需要将$criteria
调入模型。就像这样
$rows = MODEL ::model()->findAll($criteria);
其中MODEL
是要应用条件的表的模型类。
要了解更多信息,您可以关注此CActiveRecord课程。
尝试在CDbCriteria
中设置together
...
$criteria->together = true;
$model = Question::model()->findAll($criteria);
当您使用"作为计数器"时,您的模型必须具有一个名为"Counter"的属性,否则它不会将其加载到模型中。
如果您没有名为"counter"的属性,请尝试使用您现在未选择的另一个模型属性:"as someColumn"
并使用条件或添加条件或..而不是具有
干杯