# Find\_by\_sql order by date field issue **URL:** \ **Category:** Rails\ **Created:** [June 27, 2008, 3:01am UTC](https://www.ruby-forum.com/t/find-by-sql-order-by-date-field-issue/141563 "2008-06-27T03:01:05Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [June 27, 2008, 3:01am UTC](https://www.ruby-forum.com/t/find-by-sql-order-by-date-field-issue/141563/1 "2008-06-27T03:01:05Z")
I have a giant query that union around 10 tables together. Each of the queries consists of joins too. At the end I do a order by date field, time field. If I run this query in SQL alone it works just fine and if I run it on RoR it works except the order is off. I am not sure why! Some of the time fields are blank and that may be part of the issue but I understand it can be an eager loading issue. anyone have any idea how to get around this problem? ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [June 27, 2008, 3:08am UTC](https://www.ruby-forum.com/t/find-by-sql-order-by-date-field-issue/141563/2 "2008-06-27T03:08:06Z")
On Thu, 2008-06-26 at 18:00 -0700, David wrote: > I have a giant query that union around 10 tables together. Each of > the queries consists of joins too. At the end I do a order by date > field, time field. If I run this query in SQL alone it works just > fine and if I run it on RoR it works except the order is off. I am > not sure why! > > Some of the time fields are blank and that may be part of the issue > but I understand it can be an eager loading issue. anyone have any > idea how to get around this problem? * * * compare the log file SQL statement with the SQL statement you are running by itself because the answer is that they are going to be different and the fix should become more obvious. Craig ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [June 27, 2008, 3:32am UTC](https://www.ruby-forum.com/t/find-by-sql-order-by-date-field-issue/141563/3 "2008-06-27T03:32:40Z")
On Thu, 2008-06-26 at 18:26 -0700, David wrote: > Thanks for the idea. I tried and it looks exactly the same… * * * if it is the same, then the order would be the same Craig ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [June 27, 2008, 3:27am UTC](https://www.ruby-forum.com/t/find-by-sql-order-by-date-field-issue/141563/4 "2008-06-27T03:27:24Z")
Thanks for the idea. I tried and it looks exactly the same… ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [June 27, 2008, 3:36am UTC](https://www.ruby-forum.com/t/find-by-sql-order-by-date-field-issue/141563/5 "2008-06-27T03:36:11Z")
It seems like RoR is ignoring the order by command on the SQL statement…or the data returned is not of type date. This is making me crazy! I am sure I will kick myself once it is fixed because it will be obvious… ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [June 27, 2008, 4:08am UTC](https://www.ruby-forum.com/t/find-by-sql-order-by-date-field-issue/141563/6 "2008-06-27T04:08:56Z")
Just when I think it cannot get stranger it does. I can sort by anything but the date field. I wonder if I need to convert it to something else and it is looking at it as something other than DATE…hmmm ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [June 27, 2008, 11:52am UTC](https://www.ruby-forum.com/t/find-by-sql-order-by-date-field-issue/141563/7 "2008-06-27T11:52:36Z")
Anyone have any ideas? ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [June 27, 2008, 11:58am UTC](https://www.ruby-forum.com/t/find-by-sql-order-by-date-field-issue/141563/8 "2008-06-27T11:58:27Z")
On 27 Jun 2008, at 10:51, David wrote: > Anyone have any ideas? If you could post a query which reproduces the problem (try and reduce it to the minimal example) that would probably help because until now it’s just been handwaving. Fred ---
**Author:** ![david](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/aeb1de/32.png) [@david](https://www.ruby-forum.com/u/david)\ **Post date:** [June 27, 2008, 12:21pm UTC](https://www.ruby-forum.com/t/find-by-sql-order-by-date-field-issue/141563/9 "2008-06-27T12:21:54Z")
Here is an extremely shortened example… @ttemp1=Time.now @year = @ttemp1.year @month = @ttemp1.month @day = @ttemp1.day @temptdate=Time.parse(Time.now.to\_s) @tempsdate=@temptdate.to\_date - 1 @tempedate=@temptdate.to\_date + (@papreference[0].keyword.to\_i - 1) @events=Uat.find\_by\_sql(“select appointmentrecs.AppointmentName as subject, appointmentrecs.id as linkid, appointmentrecs.StartDate as sdate, appointmentrecs.StartTime as sTime, appointmentrecs.TZEndDate as eDate, appointmentrecs.EndTime as eTime,appointmentrecs.Priority as DPriority, appointmentrecs.uat\_id as uat\_id, (removed code here) as link, CONCAT(keywords.keyword,’-’,kw2.keyword) as lntype from appointmentrecs left join appointments on appointments.id=appointmentrecs.AppointmentParent left join keywords on appointments.RepeatAppointment=keywords.id left join keywords as kw2 on appointments.AppointmentType=kw2.id where (appointmentrecs.StartDate \>= ‘“removed\_email\_address@domain.invalid\_s+”’ and appointmentrecs.StartDate \<= ‘“removed\_email\_address@domain.invalid\_s+”’) and appointmentrecs.uat\_id=”+session[:user\_id].to\_s+" and appointmentrecs.AppointmentParentLink\<\>‘Y’ union …(there are around 10 union queries here but I removed them to shorten this) select appointmentrecs.AppointmentName as subject, appointmentrecs.id as linkid, appointmentrecs.StartDate as sdate,appointmentrecs.StartTime as sTime, appointmentrecs.TZEndDate as eDate, appointmentrecs.EndTime as eTime,appointmentrecs.Priority as DPriority, appointmentrecs.uat\_id as uat\_id, (removed code here) as link, CONCAT(keywords.keyword,’-’,kw2.keyword) as lntype from appointmentrecs left join appointments on appointments.id=appointmentrecs.AppointmentParent left join keywords on appointments.RepeatAppointment=keywords.id left join keywords as kw2 on appointments.AppointmentType=kw2.id where (appointmentrecs.StartDate \>= ‘“removed\_email\_address@domain.invalid\_s+”’ and appointmentrecs.StartDate \<= ‘“removed\_email\_address@domain.invalid\_s+”’) and appointmentrecs.uat\_id="+session[:user\_id].to\_s+" and appointmentrecs.AppointmentParentLink=‘Y’ ORDER BY sdate,sTime")