CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
Jeff Waltzer
Jeff Waltzer

Reputation: 527

Column order of results from rails ActiveRecord find_by_sql call

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?

Upvotes: 4

Views: 2833

Answers (4)

Avdi
Avdi

Reputation: 18418

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.

Upvotes: 3

Stan Brajewski
Stan Brajewski

Reputation: 462

In rails 3.2 and higher you can use attribute_names for each record of find_by_sql results. This is documented in find_by_sql:

Executes a custom SQL query against your database and returns all the results. The results will be returned as an array with columns requested encapsulated as attributes of the model you call this method from. If you call Product.find_by_sql then the results will be returned in a Product object with the attributes you specified in the SQL query.

If you call a complicated SQL query which spans multiple tables the columns specified by the SELECT will be attributes of the model, whether or not they are columns of the corresponding table

For Models you can use column_names. For more info on the variations see other SA answer: How do you discover model attributes in Rails

Upvotes: 1

Mike Woodhouse
Mike Woodhouse

Reputation: 52326

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 %>

Upvotes: 2

Mike Deck
Mike Deck

Reputation: 18397

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.

Upvotes: 0

Related Questions