Yii 查询没有返回结果 [英] Yii query returning no results

问题描述

我正在努力在 Yii 中创建正确的查询，但我相信我正在取得进展.

I'm struggling to create the correct query in Yii, I believe I'm making progress though.

我需要检查相关表，只想返回相关表中没有记录的记录.这是在这里回答 - Yii 确定相关模型的存在

I need to check a related table and only want to return records that don't have a record in the related table. This was answered here- Yii determining existence of related models

使这个问题复杂化并且我不确定如何克服它的是多个用户可以在此相关表中拥有记录.因此，完整的要求是返回不存在相关记录的记录，而只计算登录用户的记录.

What is complicating this and I'm unsure how to overcome it, is that multiple users can have records in this related table. Therefore the full requirement is to return records where no related record exists, but only counting records for the logged in users.

两个相关的对象如下——调查问卷已回答问题

The two related objects are as follows- SurveyQuestion AnsweredQuestion

SurveyQuestion HAS_MANY AnsweredQuestion

SurveyQuestion HAS_MANY AnsweredQuestion

AnsweredQuestion 表具有以下列-

AnsweredQuestion table has the following columns-

id -survey_question_id - 用户 ID

id - survey_question_id - user_id

survey_question_id 是 SurveyQuestion 表的外键.

survey_question_id is the foreign key for the SurveyQuestion table.

到目前为止，我的方法是尝试将记录限制为与具有关系定义的登录用户相关的记录-

My approach so far is to try and limit the records to those relevant to the logged in user with the relation definition-

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 函数中使用了一个条件，例如 -

To limit the the query to records in the parent table with no relevant ones in the child table I've used a condition in the findAll function like so-

            $questions = SurveyQuestion::model()->with(array(
                                            'survey_answer',
                                            'answered_questions'=>array(

                                                    'select'=>false,

                                                    'joinType'=>'LEFT JOIN',
                                                    'condition'=>'`answered_questions` . `id` is NULL'
                                                    ),))->findAll();

这两段代码即使清除了子表也没有返回结果.

The two pieces of code return no results even when the child table is cleared.

有人能发现我在方法或执行上哪里出错了吗?

Can anyone spot where I'm going wrong, in approach or execution?

非常感谢，

尼克

更新

根据要求，这是运行的 sql 语句.相关的是第二个 Join，第一个 Join 收集多项选择答案.

As requested, here's the sql statement that is run. It's the second Join that is relevant, the first Join collects the multiple choice answers.

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

推荐答案

在我发布了关于以可视化方式运行查询的评论后，我有了一个想法.

Had a though after I posted the comment about visually running your query.

我认为您需要将 user_id 的条件放在关系的 on 子句中，而不是 condition 子句中.因为它只返回子行 NULL id 和 user_id 为 2 的父行.这显然永远不会发生.但是你需要它在 JOIN 标准中.所以它应该是:

I think you need to put the condition for user_id in the on clause of the relation, rather than the condition clause. Because it's only returning parent rows where the child has NULL id and user_id of 2. This will obviously never happen. But you need it to be in the JOIN criteria. So it should read:

'answered_questions' => array(self::HAS_MANY, 'AnsweredQuestion', 'question_id',
    'on'=>'answered_questions.user_id = '.Yii::app()->user->id,
    'joinType'=>'LEFT JOIN',
),

这篇关于Yii 查询没有返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！