How do you get the rows and the columns in the result of a query with ActiveRecord?

Question

Is there a way with ActiveRecord to execute a custom SQL query and have it return an array of arrays where the first row is the column names and each following row is the row data? I want to execute something like:

connection.select_rows_with_headers "SELECT id, concat(first_name, ' ', last_name) as name, email FROM users"

And have it return:

[["id","name","email"],["1","Bob Johnson","[email protected]"],["2","Joe Smith","[email protected]"]]

This would allow me to print the results of the custom query in an HTML table like this:

<table>
  <% result.each_with_index do |r,i| %>
    <tr>
      <% r.each do |c| %>
        <% if i == 0 %>
          <th><%=h c %></th>
        <% else %>
          <td><%=h c %></td>
        <% end %> 
      <% end %>
    </tr>
  <% end %>
</table>

Note that select_all doesn't work because the keys in each hash are unordered, so you've lost the ordering of the results as specified in the query.

Answer 1

Not EXACTLY what you're looking for, but maybe:

connection.execute('select * from users').all_hashes

and you'll get back

[{:id => 1, :name => 'Bob', :email => '[email protected]'},{:id => 1, :name => 'Joe', :email => '[email protected]'}]

and you could do:

results = connection.execute('select * from users').all_hashes
munged_results = []
columns = results.first.keys.map(&:to_s)
munged_results << results.first.keys.map(&:to_s)
munged_results += results.map{|r| columns.map{|c| r[c]} }

something like that

edit:

results = connection.execute('select * from users').all_hashes
munged_results = []
columns = User.column_names
munged_results << columns
munged_results += results.map{|r| columns.map{|c| r[c]} }

That should be ordered properly.

Beyond that there is the result object that is returned from #execute that can be interrogated for bits of information. Methods like #fetch_fields get you the fields in order and #fetch_row will get you each row of the result set as an array (works like an iterator).

edit again:

OK, here's a good solution, modify for whatever DB you're using:

class Mysql::Result
  def all_arrays
    results = []
    results << fetch_fields.map{|f| f.name}

    while r = fetch_row
      results << r
    end

    results
  end
end

That will get them without a ton of overhead.

Use it like this:

connection.execute('select salt, id from users').all_arrays