Forum: Ruby on Rails Retaining the order of columns in a result set

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
36e6abb79962f4c941673e480876d821?d=identicon&s=25 austin (Guest)
on 2005-11-23 23:50
(Received via mailing list)
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
821395fe70906c8290df7f18ac4ac6cf?d=identicon&s=25 technoweenie (Guest)
on 2005-11-23 23:58
(Received via mailing list)
> 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
A2b2f4ee23989dc68529baef9cbddcd6?d=identicon&s=25 listbox (Guest)
on 2005-11-25 17:07
(Received via mailing list)
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
36e6abb79962f4c941673e480876d821?d=identicon&s=25 austin (Guest)
on 2005-11-25 22:42
(Received via mailing list)
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")
This topic is locked and can not be replied to.