# Find\_by\_sql column ordering defect?

**URL:** <https://www.ruby-forum.com/t/find-by-sql-column-ordering-defect/52649>\
**Category:** Rails\
**Created:** [January 17, 2006, 3:27pm UTC](https://www.ruby-forum.com/t/find-by-sql-column-ordering-defect/52649 "2006-01-17T15:27:45Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Henning\_KSPedersen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/73ab20/32.png) [@Henning\_KSPedersen](https://www.ruby-forum.com/u/Henning_KSPedersen)\
**Post date:** [January 17, 2006, 3:27pm UTC](https://www.ruby-forum.com/t/find-by-sql-column-ordering-defect/52649/1 "2006-01-17T15:27:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Henning\_KSPedersen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/73ab20/32.png) [@Henning\_KSPedersen](https://www.ruby-forum.com/u/Henning_KSPedersen)\
**Post date:** [January 17, 2006, 3:33pm UTC](https://www.ruby-forum.com/t/find-by-sql-column-ordering-defect/52649/2 "2006-01-17T15:33:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Henning\_KSPedersen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/73ab20/32.png) [@Henning\_KSPedersen](https://www.ruby-forum.com/u/Henning_KSPedersen)\
**Post date:** [January 17, 2006, 3:57pm UTC](https://www.ruby-forum.com/t/find-by-sql-column-ordering-defect/52649/3 "2006-01-17T15:57:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Henning\_KSPedersen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/73ab20/32.png) [@Henning\_KSPedersen](https://www.ruby-forum.com/u/Henning_KSPedersen)\
**Post date:** [January 17, 2006, 4:19pm UTC](https://www.ruby-forum.com/t/find-by-sql-column-ordering-defect/52649/4 "2006-01-17T16:19:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Henning\_KSPedersen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/73ab20/32.png) [@Henning\_KSPedersen](https://www.ruby-forum.com/u/Henning_KSPedersen)\
**Post date:** [January 17, 2006, 4:12pm UTC](https://www.ruby-forum.com/t/find-by-sql-column-ordering-defect/52649/5 "2006-01-17T16:12:59Z")

</div>

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…