将Rails3.2基于Squeel的数据库查询改写为Rails6 Active Record语法求助

我正在将原有基于Rails 3.2开发的应用升级到Ruby on Rails 6.1.4.2版本，原应用中的复杂数据库查询使用Squeel gem的专属DSL语法编写，多日尝试都未能将其正确改写为适配Rails 6的Active Record合法语法，以下是两个查询的适配方案：

查询1 #

原Squeel写法 #

Project.joins{vendor}.joins{certifications.outer}.where{
        (projects.vendor_id.eq my{ vendor_id }) |
        (vendors.parent_vendor_id.eq my{ vendor_id }) |
        ((certifications.cdti == true) & (certifications.published == true))
      }.uniq

对应Rails 3.2输出的原生SQL #

SELECT DISTINCT "vendors".* FROM "vendors" INNER JOIN "projects" ON "projects"."vendor_id" = "vendors"."id" 
INNER JOIN "certifications" ON "certifications"."project_id" = "projects"."id" 
WHERE (("certifications"."cdti" = 't' AND "certifications"."published" = 't')) 
ORDER BY "vendors"."parent_vendor_id", "vendors"."name"

Rails 6适配后的Active Record写法 #

直接使用Rails原生Arel接口实现复杂或/与条件拼接，左连接用left_joins方法，去重用distinct方法：

# 此处vendor_id替换为你实际业务上下文中的变量
vendor_id = current_vendor.id 

Project.joins(:vendor)
       .left_joins(:certifications)
       .where(
         Project.arel_table[:vendor_id].eq(vendor_id)
         .or(Vendor.arel_table[:parent_vendor_id].eq(vendor_id))
         .or(
           Certification.arel_table[:cdti].eq(true)
           .and(Certification.arel_table[:published].eq(true))
         )
       )
       .distinct

查询2 #

原Squeel写法 #

Fleet.joins{vendor.projects.certifications}.
        where{(certifications.cdti.eq true) & (certifications.published.eq true)}.
        uniq.includes(:vendor).
        order(:vendor_id, :name)

对应Rails 3.2输出的原生SQL #

SELECT DISTINCT "fleets".* FROM "fleets" INNER JOIN "vendors" ON "vendors"."id" = "fleets"."vendor_id" 
INNER JOIN "projects" ON "projects"."vendor_id" = "vendors"."id" 
INNER JOIN "certifications" ON "certifications"."project_id" = "projects"."id" 
WHERE (("certifications"."cdti" = 't' AND "certifications"."published" = 't')) 
ORDER BY "fleets"."vendor_id", "fleets"."name"

Rails 6适配后的Active Record写法 #

嵌套关联直接用Hash格式的joins声明，全是且关系的条件直接用Hash写法即可：

Fleet.joins(vendor: { projects: :certifications })
     .where(certifications: { cdti: true, published: true })
     .distinct
     .includes(:vendor)
     .order(:vendor_id, :name)

核心适配说明 #

• Squeel的DSL本质是对Arel的封装，Rails 6原生支持直接调用Arel接口实现复杂的与或查询条件，不需要额外依赖
• 嵌套关联的join可以直接用Hash写法简化实现，不需要Squeel的块语法
• left_joins 方法对应原Squeel的outer 左连接声明
• distinct 方法替代原Rails 3的uniq 去重声明

内容的提问来源于stack exchange，提问作者Scott Milella