Find_by_sql column ordering defect?

Rails
1

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? :slight_smile:

Regards,
Henning P.

2

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

3

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 W.

4

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.

5

Tom W. 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…