title: ActiveRecord Arel vs SQL and best practices
body: <p>Today I learned about how to use Arel to construct SQL queries that would require SQL fragments with standard ActiveRecord. Until now, all Rails apps I've seen have (at best) scopes which wrap raw SQL, like these:</p>
  
  <pre><code># in class Post
  scope :select_comment_count, -&gt; {
    join_comments.select('COUNT(comments.id)')
  }
  scope :join_comments, -&gt; {
    joins("LEFT OUTER JOIN comments ON comments.post_id = posts.id AND comments.is_most_recent_edit = '1'")
  }
  </code></pre>
  
  <p>Which can both be rewritten in Arel using no SQL.</p>
  
  <p>My first question is, what are the concrete advantages and disadvantages of using Arel over SQL fragments, and why does is seem as though every app and every RoR developer ignores Arel?</p>
  
  <p>Also, my Arel seems to be really messy by default, partly because my Arel has to have knowledge of my foreign key names:</p>
  
  <pre><code>scope :select_comment_count, -&gt; {
    comments = Comment.arel_table
    joins_comments.select(comments[:id].count)
  }
  
  scope :join_comments, -&gt; {
    posts = Post.arel_table
    comments = Comment.arel_table
  
    # Bypasses ActiveRecord associations completely.
    # We're using Arel to generate the above SQL
    # Isn't this exactly the same as using Raw SQL, but slower?
    # In some cases we would still lose DB independence,
    # for instance if we did an update_all with a join in MySQL
    # (not allowed in PostgreSQL)
  
    sql = posts.
      join(comments, Arel::Nodes::OuterJoin).
      on(
            (comments[:post_id].eq(posts[:id])). # Here we duplicate the knowledge of how our foreign key names relate to our associations
        .and(comments[:is_most_recent_edit].eq(false))
      ).join_sql
  
    joins(join_sql)
  }
  </code></pre>
  
  <p>Are there any good tools or libraries out there for unifying the ActiveRecord query interface and Arel more, or any good techniques for keeping your Arel simple and beautiful? I'd really like to use Arel, but in a way that harnesses the power of ActiveRecord associations and doesn't feel like a completely separate API or an extra layer of complication above SQL.</p>