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 say, 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!