Format custom SQL queries in Rails

While Rails comes with ActiveRecords that saves us from writing some scary (at least to me :)) SQL logic but still there are times when we need to write raw SQL queries. We have well defined coding conventions as far as ruby is concerned. But we usually pay little attention to formatting the SQL queries. This makes it more difficult to read them.

Many developers tend use a readability-wise bad code. Here is one example:

@start_date = Time.parse(start_date).to_date.beginning_of_day
@end_date = Time.parse(end_date).to_date.end_of_day

sql_query = "select u1.name,im.lat,
im.long from  users as u1 JOIN  images as 
im ON u1.id= im.imageable_id where  u1.created_at  
BETWEEN '#{@from_date}' AND '#{@till_date}' ;" 

query_result = ActiveRecord::Base.connection.execute(sql_query)


The alternative to this is using heredoc. The heredoc nicely explains about how to write multiple strings involving HTML or SQL snippets. The heredoc syntax is very useful for writing multi-line strings and avoiding quoting issues. Hence they can also be used for writing clean SQL queries.

Before

Let’s take a look at following snippet involving raw SQL, where heredoc syntax is used:

def query source_id, tags
      <<-SQL
        SELECT articles.id FROM articles
          INNER JOIN taggings
          ON taggings.taggable_id = articles.id
            AND taggings.context = 'tags'
            AND taggings.taggable_type = 'Article'
          INNER JOIN tags
            ON tags.id = taggings.tag_id
          INNER JOIN source_articles
            ON articles.id = source_articles.article_id
            GROUP BY source_articles.source_id, articles.id
            HAVING source_articles.source_id = #{source_id}
              AND articles.state = 0
              AND (
              (array#{tags}) && (ARRAY_AGG(tags.id))
              )
      SQL
end 


From the above snippet one can understand what’s going on here but that takes considerable efforts for sure.

After

As they there is always a room for improvement, we can still improve the readability of above code snippet. While there are multiple different ways to format the SQL queries and one can argue about its simplicity, we found this one as most simple.

def query source_id, tags
  <<-SQL
        SELECT articles.id
          FROM articles
    INNER JOIN taggings
            ON taggings.taggable_id = articles.id
           AND taggings.context = 'tags'
           AND taggings.taggable_type = 'Article'
    INNER JOIN tags
            ON tags.id = taggings.tag_id
    INNER JOIN source_articles
            ON articles.id = source_articles.article_id
      GROUP BY source_articles.source_id, articles.id
        HAVING source_articles.source_id = #{source_id}
           AND articles.state = 0
           AND (array(#{tags}) && (ARRAY_AGG(tags.id)))
  SQL
end

A simple formatting can make a piece of code more readable. This can make a developer’s life a lot more easy!

Leave a Comment

Your email address will not be published. Required fields are marked *