如何将此原始查询转换为活动记录查询界面或Arel？

问题描述

我想找到个课程，其中至少有1个 variant 具有 variant_type = 1并且没有任何 variant 且 variant_type = 2。

I want to find courses which have at least 1 variant with variant_type = 1 and don't have any variant with variant_type = 2.

所以我的查询如下：

Course.where("id IN ( SELECT course_id
                     FROM course_variants
                     WHERE variant_type = 1
                   )
             AND id NOT IN (
                     SELECT course_id
                     FROM course_variants
                     WHERE variant_type = 2
                   )")

另外，还有一个课程有很多 course_variants 。

Additionally, a course has many course_variants.

我在where子句中使用原始查询，我想使用Active对其进行改进记录接口或Arel，对此有什么解决方案？

I'm using a raw query in where clause, I want to improve this using Active record interface or Arel, any solution for this?

谢谢！

输入

course: {id=1, course_variants: [{variant_type: 1}, {variant_type: 2}]}
course: {id=2, course_variants: [{variant_type: 1}, {variant_type: 3}]}
course: {id=3, course_variants: [{variant_type: 2}, {variant_type: 3}]}

输出

course: {id=2, course_variants: [{variant_type: 1}, {variant_type: 3}]}

推荐答案

您可以对模型关联进行一些调整：

You may tweak your model association a bit:

class Course < ActiveRecord::Base
  has_many :type_1_variants, class_name: "CourseVariant", -> { where(variant_type: 1) }
  has_many :non_type_3_variants, class_name: "CourseVariant", -> { where.not(variant_type: 3) }
end

Course.joins(:type_1_variants, :non_type_3_variants).group(:course_id).having('COUNT(course_variants.id) > 0').having('COUNT(non_type_3_variants_courses.id) > 0')

需要用执行连接时ARel生成的适当别名替换'non_type_3_variants_courses'（我没有Rails env atm）

You may need to replace 'non_type_3_variants_courses' with the proper alias name that ARel generates when doing joins (I don't have a Rails env atm)

这篇关于如何将此原始查询转换为活动记录查询界面或Arel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！