Running Free on Rails: Displaying results of find_by_sql queries

When we construct views of query results using ERb in rails, most of the time we know what we are displaying and how to format it. However, there are a few occasions when we run find_by_sql queries. Even rare are the occasions when we let users type free-form SQL queries into an input box and just take the query string as is and run it on the database (MySQL in my case).

The reason I did the free-form queries in our internal site was to let users crunch the data the way they wanted instead of trying to add more and more views as they kept requesting them. However, this presents a problem in the View portion (of the Model-View-Controller).

Since we do not know what columns the user has actually requested in the query, it is not easy to figure out how to write the ERb/HTML view code. Fortunately, the attribute_names method of the model helps here.

So you do this in your controller:

def sqlquery
  if (!querystr.blank?)
    @results = Model.find_by_sql(querystr)
    @total = @results.size()
     if (@total > 0)
       @columns = @results.first.attribute_names
     end
   else
      @total = 0
   end
end

The attribute_names is very useful since it returns exactly the columns that are requested in the SELECT portion of the query (rather than every column in the Model model). Also, when the SELECT uses a AS clause (please refer to the MySQL SQL documentation), the attribute_names contains the string in the AS clause. e.g. If the query has SELECT name AS ‘UserName’ … then attribute_names returns UserName as one of the columns.

However, I still faced one gotcha that I could not figure out either by searching or by reading the rails source. The attribute_names method does not return columns in the order they were specified in the SELECT clause. It can return them sorted alphabetically, but that’s it! Now, users do not like see columns in random order showing up in the results table. I thought of parsing the query but then realized it was futile since the query could contain recursive SQL and table joins etc. The easiest hack that I found is to just try string matching which surprisingly works for all our SQL queries. I do not see a reason why it should not for all cases. So the new code looks like this:

def sqlquery
  if (!querystr.blank?)
    @results = Model.find_by_sql(querystr)
    @total = @results.size()
    if (@total > 0)
        cols = @results.first.attribute_names
        if (querystring.match(/\s*SELECT\s*\*\s*FROM.*/i))
            @columns = cols
        else
            pos = cols.inject({}) { |h, col| h[col] = querystr.index(col); h}
            @columns = cols.sort { |x,y| pos[x] < => pos[y]}
        end
     else
         @total = 0
     end
end

So what does the code do? First, we check if the query was a SELECT * FROM which will not have any column names in the SELECT clause. Then we just rely on the random order of the columns returned by attribute_names. If not, we compute the position of the strings returned by the attribute_names in the original query (in the cols.inject line). They could be simple column names or even the AS clauses. We can now use the position(index) of the substring as a sorting criteria so that the earlier occurring column names in the SELECT clause is earlier in the @columns array. This seems like a hack but it seems to work everytime. If there is a more elegant way, I am all ears…

Now in the view, we can use the @columns array for our view (do not forget to change the code below from table to <table>, td to <td>, etc.):

table
thead
  < % for col in @columns %>
       td < %= col %> /td
   < % end %>
   /tr
/thead
      < % for item in @results %>
        tr
        < % for col in @columns %>
          td < %= item[col] %> /td
        < % end %>
        /tr
      < % end %>
/table