Arel join子查询

Question

我是Rails和Arel的新手，我创建了一个连接查询，但看起来写得很糟糕

join_query = 
ActiveRecord::Base.connection.execute <<~SQL.strip_heredoc
        (
          SELECT schools.name, sum(q.enrollment), count(*) FROM schools LEFT JOIN
            (
              SELECT sites.school_id, sites.enrollment  FROM sites JOIN projects ON
              sites.project_id = projects.id
              WHERE sites.school_id IS NOT NULL and projects.type IN ('Startup') AND projects.deleted_at IS NULL
            ) q
          ON q.school_id = schools.id
          GROUP BY schools.name
        )
      SQL
join_query.values

这是如何使用Arel编写的？

另外，你能给我指出这方面的一个好的教程吗？

提前感谢

Answer 1

这就是我能想出的给定的SQL：

schools = Arel::Table.new("schools")
sites = Arel::Table.new("sites")
projects = Arel::Table.new("projects")

join = sites.project(:school_id, :enrollment)
  .join(projects).on(sites[:project_id].eq(projects[:id]))   
  .where(
    sites[:school_id].not_eq(nil)
    .and(projects[:type].in("Startup"))
    .and(projects[:deleted_at].eq(nil))
  )

q = Arel::Table.new(join).alias(:q)

schools.project(:name, q[:enrollment].sum, Arel.star.count)
  .join(join, Arel::Nodes::OuterJoin).on(q[:school_id].eq(schools[:id]))
  .group(schools[:name])

作为一个新手，以下是我经常访问的一些参考资料：

https://www.rubydoc.info/github/rails/arel (官方文档)

https://devhints.io/arel (一个非常有用但不完整的小抄)

https://gist.github.com/mildmojo/3724189 (左外部联接示例)

https://jpospisil.com/2014/06/16/the-definitive-guide-to-arel-the-sql-manager-for-ruby.html (Arel综合指南)