← Back to Upcase

Formatting SQL in Ruby


(Geoff Harcourt) #1

I have some SQL queries that can’t conveniently be done in ActiveRecord or Arel. Some of these queries have long lines. For the sake of my logs, it seems like it would be preferable to have the query compacted without line breaks, but when looking at the query for editing/comprehension, it is advantageous to have the query broken out into pieces. I have two questions:

  1. Is there a good set of style guidelines anyone uses for how to break a SQL query up?
  2. Should I be doing something like gsub-ing the line breaks out to make the query compact when it gets run?

(Leonardo Brito) #2

The simple and fast answer is to use prepared statements, so you’ll only interpret the long query once, thus no performance penalty for writing it in pieces.
You can also simplify your queries using views, in a way to reflect better your domain.
if you’re not comfortable with gsub you can try:
%w{ query_line_1
query_line_2
query_line_3… }.join(’ ')
Finally I recommend this awesome presentation: http://www.confreaks.com/videos/3332-railsconf-advanced-arel-when-activerecord-just-isn-t-enough