Retaining the order of columns in a result set

Rails
1

HI,

I am trying to get the ordered column names in a result set using
find_by_sql. Perhaps my approach is all wrong, but I am using
each .. do. How do I go about keeping the order of the columns as
they appear in the SELECT statement?

For example:

@rows = SomeConnection.find_by_sql "SELECT one,two,three,four FROM

some_table"

@rows.each do |row|
 		$test = row.attribute_names.join(",")
end

…

<%=$test%>

produces something like:

        four,one,three,two

What I would like is for them to keep their order as they appear in
the select statement:

        one,two,three,four

…

I hope that is a clear question. Thanks for giving your help!

Austin

2

I am trying to get the ordered column names in a result set using
find_by_sql. Perhaps my approach is all wrong, but I am using
each .. do. How do I go about keeping the order of the columns as
they appear in the SELECT statement?

The recordset comes back as a hash, so naturally they’re out of order.
Why not just display the columns in the order you want?

@rows = Person.find_by_sql “SELECT * FROM people;”

<% @rows.each do |row| -%>
<% %w( id name age ).each do |col| -%>
<%= row.send(col) %>
<% end -%>
<% end -%>

rick
http://techno-weenie.net

3

On 23-nov-2005, at 23:47, Austin Swinney wrote:

…

I hope that is a clear question. Thanks for giving your help!
One option would be to use the functionality fo the underlying driver
to get the query result in the form that you want. However, you can
use some reflection
to use the order provided by ActiveRecord itself (it might be not
applicable to your own case)

Person.columns.collect{|c| c.name}
=> [“id”, “login”, “name”, “password”, “email”, “busy”, “cv”,
“invited_by_id”, “invites_left”, “admin”, “region_id”]

This is aliased as Person.column_names, which makes you rquery into:

@rows = SomeModel.find_by_sql "SELECT one,two,three,four FROM

some_table"

@rows.each do |row|
	for column in SomeModel.columns
		#iterate through columns in natural order
	end
end
4

thanks for the response, julik,

i’m writing an export script that pulls an unknown query and needs to
retain the column names and the column order used in the sql for the
purpose of outputting a spreadsheet to the browser.

that is why this causing me so much grief. i’m using a generic db
connection, not any specific table. i just know the database, but
the query is a join on at least two tables out of many.

i did something similiar to what i am trying to do here with a ruby
script. it uses fetch_fields to get the column info on the result set
after the query has been made. that is essentially what i am looking
for in rails, column information on a query result.

xsql is some unknown query

 res_xc = dbh.query(xsql)
 @cols = []
 res_xc.fetch_fields.each_with_index do |info,i|
   @cols << info.name

witness the power of info

printf “— Column %d (%s) —\n”, i, info.name

printf “table: %s\n”, info.table

printf “def: %s\n”, info.def

printf “type: %s\n”, info.type

printf “length: %s\n”, info.length

printf “max_length: %s\n”, info.max_length

printf “flags: %s\n”, info.flags

printf “decimals: %s\n”, info.decimals

  end

 @line = []
 @line << "\n\r"
 @line << @cols.join("\t")
 while row = res_xc.fetch_row do
   @col = []
   row.each do |v|
     @col <<  v
   end
   @line << @col.join("\t")
 end
 file = @line.join("\n")