gpt4 book ai didi

mysql - as_json 忽略 .where() 子句的第二部分

转载 作者:太空宇宙 更新时间:2023-11-03 16:16:28 24 4
gpt4 key购买 nike

我正在尝试从包含 5 个表的现有数据库生成一个 json 结构::用户:资源:quiz_questions:测验答案:quiz_responses

一个资源有很多 quiz_questions,它有很多 quiz_answers,然后有很多 quiz_responses。用户也有_许多测验_响应。 (这个想法是用户进行多项选择测验,然后他们选择现有答案,然后在 quiz_responses 中创建一个新行。

所以我有两行代码:

questions = QuizQuestion.includes(:quiz_answers, :quiz_responses).where(resource_id: 623, quiz_responses: {user_id: 18276})

产生这个查询:

SELECT 
`quiz_questions`.`id` AS t0_r0,
`quiz_questions`.`question` AS t0_r1
`quiz_questions`.`resource_id` AS t0_r2,
`quiz_questions`.`created_at` AS t0_r3,
`quiz_questions`.`updated_at` AS t0_r4,
`quiz_questions`.`question_type` AS t0_r5,
`quiz_questions`.`url` AS t0_r6,
`quiz_questions`.`auto_next` AS t0_r7,
`quiz_questions`.`show_correct` AS t0_r8,
`quiz_questions`.`answer_type` AS t0_r9,
`quiz_answers`.`id` AS t1_r0,
`quiz_answers`.`answer` AS t1_r1,
`quiz_answers`.`quiz_question_id` AS t1_r2,
`quiz_answers`.`correct` AS t1_r3,
`quiz_answers`.`created_at` AS t1_r4,
`quiz_answers`.`updated_at` AS t1_r5,
`quiz_answers`.`answer_immediately` AS t1_r6,
`quiz_answers`.`time_limit` AS t1_r7,
`quiz_responses`.`id` AS t2_r0,
`quiz_responses`.`user_id` AS t2_r1,
`quiz_responses`.`quiz_answer_id` AS t2_r2,
`quiz_responses`.`created_at` AS t2_r3,
`quiz_responses`.`updated_at` AS t2_r4,
`quiz_responses`.`attempt_id` AS t2_r5,
`quiz_responses`.`video_url` AS t2_r6,
`quiz_responses`.`correct` AS t2_r7,
`quiz_responses`.`group_id` AS t2_r8
FROM `quiz_questions`
LEFT OUTER JOIN `quiz_answers` ON `quiz_answers`.`quiz_question_id` = `quiz_questions`.`id`
LEFT OUTER JOIN `quiz_answers` `quiz_answers_quiz_questions_join` ON `quiz_answers_quiz_questions_join`.`quiz_question_id` = `quiz_questions`.`id`
LEFT OUTER JOIN `quiz_responses` ON `quiz_responses`.`quiz_answer_id` = `quiz_answers_quiz_questions_join`.`id`
WHERE
`quiz_questions`.`resource_id` = 623
AND `quiz_responses`.`user_id` = 18276

第二行代码:

questions.as_json(include: { quiz_answers: { include: [:quiz_responses]}})

调用这些额外的查询:

  QuizResponse Load (0.8ms)  SELECT `quiz_responses`.* FROM `quiz_responses`  WHERE `quiz_responses`.`quiz_answer_id` = 755

QuizResponse Load (0.8ms) SELECT `quiz_responses`.* FROM `quiz_responses` WHERE `quiz_responses`.`quiz_answer_id` = 756

QuizResponse Load (1.5ms) SELECT `quiz_responses`.* FROM `quiz_responses` WHERE `quiz_responses`.`quiz_answer_id` = 757

QuizResponse Load (0.7ms) SELECT `quiz_responses`.* FROM `quiz_responses` WHERE `quiz_responses`.`quiz_answer_id` = 758

QuizResponse Load (0.6ms) SELECT `quiz_responses`.* FROM `quiz_responses` WHERE `quiz_responses`.`quiz_answer_id` = 759

QuizResponse Load (0.7ms) SELECT `quiz_responses`.* FROM `quiz_responses` WHERE `quiz_responses`.`quiz_answer_id` = 760

QuizResponse Load (0.6ms) SELECT `quiz_responses`.* FROM `quiz_responses` WHERE `quiz_responses`.`quiz_answer_id` = 761

QuizResponse Load (0.6ms) SELECT `quiz_responses`.* FROM `quiz_responses` WHERE `quiz_responses`.`quiz_answer_id` = 764

QuizResponse Load (0.8ms) SELECT `quiz_responses`.* FROM `quiz_responses` WHERE `quiz_responses`.`quiz_answer_id` = 765

此代码的预期目标是获取与资源 867 相关的所有问题,然后返回可能的答案以及与特定用户(在本例中为 18276)相关的响应

问题是,虽然只有与资源 867 相关的问题会作为 json 返回,但会返回所有用户的响应,而不管第一行中的 where 子句,而不仅仅是用户 18276 的响应。为什么是这样?有什么方法可以告诉 as_json 只使用它接收到的内容并返回初始查询中选择的 quiz_responses 吗?解决此问题的“rails 方式”是什么?

最佳答案

更新:新解决方案

Rails 不允许预加载参数化关联。但是可以通过两个查询加载所有必要的数据并将其解析为 json:

# load data
questions = QuizQuestion.preload(:quiz_answers)
answers_ids = questions.collect{ |question| question.quiz_answers.collect(&:id) }.flatten
responses = QuizResponse.where(quiz_answer_id: answers_ids).group_by(&:quiz_answer_id)

# making json
json_string = Jbuilder.encode do |json|
json.array! questions do |question|
json.merge! question.attributes

json.quiz_answers question.quiz_answers do |answer|
json.merge! answer.attributes

json.quiz_responses responses[answer.id] do |response|
json.merge! response.attributes
end
end
end
end

如果用户在问题中没有 quiz_response,此代码将不会从结果中省略 quiz_answer。

第一个版本:

数据结构有两个 quiz_responses 关联:

QuizQuestion 有_many :quiz_responses, through: :quiz_answersQuizAnswer 有_many :quiz_responses该查询包括 quiz_responses 作为 QuizQuestion 的关系。但是 as_json 使用 quiz_responses 作为 QuizAnswer 的关系。而且 Rails 不够智能,无法理解已经加载的必要 quiz_responses。

所有你需要做的就是以嵌套的方式重写包含,就像你想使用它一样:

questions = QuizQuestion.includes(quiz_answers: :quiz_responses)

关于mysql - as_json 忽略 .where() 子句的第二部分,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43057106/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com