Rails - Nov 2005 - Retaining the order of columns in a result set

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

> 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

On 23-nov-2005, at 23:47, Austin Swinney wrote:
>
> 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!
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

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")


On Nov 25, 2005, at 8:03 AM, Julian ''Julik'' Tarkhanov
wrote:
> 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
>
>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails