Column order of results from rails ActiveRecord find_by_sql call.

Question

I'm attempting to put together some basic report screens. I've got some fairly complicated SQL queries that I'm feeding into ActiveRecord's find_by_sql method. The problem I am having here is that I am losing the order of the columns as given in the original query. I'm assuming that this is because the Hash class does not preserve entry order of its keys.

Is there a way around this problem? Should I be using a different method then find_by_sql for my queries?

Answer 1

How are you creating these "report screens"? Are they erb templates? Are you just calling .each on columns to print them all out?

If that's the case you could override the columns() method in your models to return an ordered array.

Answer 2

I like to use Ruport for reporting. It has good ActiveRecord integration and it enables you to control column order and pretty much anything else. And it's sufficiently simple to use that I don't consider it overkill even for "basic" reports.

Answer 3

You're correct in that the Ruby Hash does not preserve order. That's part of the point, really - you access it using the key.

I assume your query is written to deliver the columns in the order that you want to output them and you were hoping to output the values via a loop? Seems like a decent enough idea, but I can't think of a way to achieve it without at least some extra work.

What I'd recommend is to explicitly access the columns by key in your template, since you're probably going to end up applying styles, formatting using helper functions like number_with_delimiter, that kind of thing.

To get something like the shortcut mentioned above, I suppose you could create an array of symbols in the order required and pull the values out of the hash in a loop. Something like this? (please excuse the potentially dodgy erb: I'm a haml user!)

<% for row in @report.rows %>
    <tr>
    <% for col in [:a, :b, :c] %>
        <td><%= row[col] %></td>
    <% end %>
    </tr>
<% end %>