find_by_sql order by date field issue

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?

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?

Thanks for the idea. I tried and it looks exactly the same....

Thanks for the idea. I tried and it looks exactly the same....

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

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

Anyone have any ideas?

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

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 >= '"+@tempsdate.to_s+"' and
appointmentrecs.StartDate <= '"+@tempedate.to_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 >= '"+@tempsdate.to_s+"' and
appointmentrecs.StartDate <= '"+@tempedate.to_s+"') and
appointmentrecs.uat_id="+session[:user_id].to_s+" and
appointmentrecs.AppointmentParentLink='Y' ORDER BY sdate,sTime")