Forum: Ruby on Rails find_by_sql column ordering defect?

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.
F47c29fe9ff9abca279419c0d10f607e?d=identicon&s=25 Henning Kilset Pedersen (Guest)
on 2006-01-17 15:27
(Received via mailing list)
Hello.

I have a program that accesses a metadata (translation) table for column
names as well as the ordering of columns for a specific model. Also in
there is a collection of bool options such as column_visible_on_form,
column_visible_on_screen_quicklist etc. The idea is that our customer
can specify to a certain extent how their layout/user interface will
appear.

To accomplish this, I use find_by_sql under the belief that this would
preserve the correct column order, like so:

columns = []
@model = ModelInternationalization.find(:all, :conditions =>
"lower(model_name) = 'project' AND column_visible_on_quick_screenlist =
true",:order => 'orderno ASC')

for m in @model
  if m.modelonly == false
    columns << m.column_name
  end
end

sqlstatement = "select id, " + columns.join(', ') + " from projects"
@objs = Project.find_by_sql(sqlstatement)

the breakpointer tells me that:
irb(#<ProjectsController:0x2aaaacb3aa80>):001:0> sqlstatement
=> "select name, begin_date, projected_end_date, actual_end_date from
projects"

which is the correct order for the sqlstatement.

I then extecute the @objs = Project.find_by_sql(sqlstatement) statement.

The ordering of the columns has been altered - thus messing up my
headers and the order of the columns:

 @objs
=> [#<Project:0x2aaaacab1dc0 @attributes={"name"=>"Mopo Testprosjekt 1",
"actual_end_date"=>nil, "projected_end_date"=>#<DateTime:
4907591/2,0,2299161>, "begin_date"=>#<DateTime:
106002130906326671/43200000000,0,2299161>}>, #<Project:0x2aaaacab1d48
@attributes={"name"=>"Unikus AS", "actual_end_date"=>nil,
"projected_end_date"=>#<DateTime: 4909055/2,0,2299161>,
"begin_date"=>#<DateTime: 53001065461126757/21600000000,0,2299161>}>]

The order of the columns does NOT reflect the order of the SQL
statement, which would be the expected behavior per the SQL standard. It
does NOT even represent the original ordering of the columns in the
database table, NOR does it represent the alphabetical ordering of the
columns.

This must surely be a bug? I've posted a bug report as well, but posting
here in case anyone has any thoughts...

Help? :)

Regards,
Henning Pedersen
Cb610750ee94ca103aef4b2dc7b1b768?d=identicon&s=25 Nick Stuart (Guest)
on 2006-01-17 15:33
(Received via mailing list)
I don't think its a bug. The thing is that the column names and values
get
stored in a hash (the attributes hash) and the order of a hash is never
guaranteed. I'm not sure how you keep the order of the fields besides
possibly hard coding them in the pages. Someone else may be able to help
out
here.

-Nick
2f46d76f0e5db4dc318b03be07ebaac4?d=identicon&s=25 Tom Ward (Guest)
on 2006-01-17 15:57
(Received via mailing list)
It's certainly not a bug.  Model attributes are unordered, no matter
what custom SQL you use.

To display the columns in a particular order in your view, using your
metadata info, you could use something like :-

@objs.each{|object|
  @model.select{|m| m.model_only == false}.each {|model_info|
    object[model_info.column_name]
  }
}

Which will pull the attributes out of your object in the order defined
by your @model (ignoring those where m.model_only == true)

I hope this is helpful,

Tom Ward
Ad7805c9fcc1f13efc6ed11251a6c4d2?d=identicon&s=25 Alex Young (Guest)
on 2006-01-17 16:12
(Received via mailing list)
Tom Ward wrote:
> It's certainly not a bug.  Model attributes are unordered, no matter
> what custom SQL you use.
This is just one of those places that the impedance mismatch hits.  SQL
columns are ordered by definition.  It's an interesting theoretical
question, especially when you bring inheritance into the equation...
Cb610750ee94ca103aef4b2dc7b1b768?d=identicon&s=25 Nick Stuart (Guest)
on 2006-01-17 16:19
(Received via mailing list)
SQL Columns Ordered, yes.
Model Attributes Ordered, no.

To different beasts here and they shouldn't be treated as one and the
same,
but as being related. (at least thats how I think)
The model is BUILT from the SQL columns, thats not the same as saying
the
model is COPIED from the SQL columns.
This topic is locked and can not be replied to.