我正在努力在yii中创建正确的查询,我相信我正在取得进步。
我需要检查一个相关的表,只想返回相关表中没有记录的记录。在这里回答了这一点 - 耶伊确定相关模型的存在
是什么使这一点复杂化,我不确定如何克服它,这是多个用户可以在此相关表中拥有记录。因此,完整的要求是返回不存在相关记录的记录,而仅计算登录用户的记录。
两个相关对象如下 - SurveyQuestion回答
SurveyQuestion has_many回答
回答表的表具有以下列 -
id -sublow_question_id -user_id
soundus_question_id是SurveyQuestion表的外键。
到目前为止,我的方法是尝试将记录限制为与使用关系定义的用户登录的用户相关的记录 -
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'survey_answer'=>array(self::HAS_MANY,'SurveyAnswer','survey_question_id'),
'answered_questions' => array(self::HAS_MANY, 'AnsweredQuestion', 'question_id',
'condition'=>'answered_questions.user_id = '.Yii::app()->user->id,
'joinType'=>'LEFT JOIN',
),
);
}
将查询限制为父表中的记录,而在子表中没有相关的查询,我在Findall函数中使用了像So-so-
的条件 $questions = SurveyQuestion::model()->with(array(
'survey_answer',
'answered_questions'=>array(
'select'=>false,
'joinType'=>'LEFT JOIN',
'condition'=>'`answered_questions` . `id` is NULL'
),))->findAll();
即使清除了子表,这两个代码即使没有结果。
任何人都可以在进近或执行中发现我出错的地方吗?
非常感谢,
尼克
update
根据要求,这是运行的SQL语句。这是第二个相关的联接,第一个联接收集了多项选择答案。
SELECT `t`.`id` AS `t0_c0`, `t`.`area_id` AS `t0_c1`,
`t`.`question_text` AS `t0_c2`, `t`.`date_question` AS `t0_c3`,
`survey_answer`.`id` AS `t1_c0`, `survey_answer`.`survey_question_id` AS
`t1_c1`, `survey_answer`.`answer_text` AS `t1_c2`, `survey_answer`.`tag_id`
AS `t1_c3` FROM `tbl_survey_questions` `t` LEFT OUTER JOIN
`tbl_survey_answers` `survey_answer` ON
(`survey_answer`.`survey_question_id`=`t`.`id`) LEFT JOIN
`tbl_answered_questions` `answered_questions` ON
(`answered_questions`.`question_id`=`t`.`id`) WHERE
((answered_questions.user_id = 2) AND (`answered_questions` . `id` is
NULL))
在我发布有关视觉运行查询的评论后,有一个。
我认为您需要将user_id
的条件放在关系的on
子句中,而不是condition
子句。因为它只是孩子具有NULL
ID和user_id
的返回父行。这显然永远不会发生。但是您需要它在JOIN
标准中。因此应该阅读:
'answered_questions' => array(self::HAS_MANY, 'AnsweredQuestion', 'question_id',
'on'=>'answered_questions.user_id = '.Yii::app()->user->id,
'joinType'=>'LEFT JOIN',
),