find_by_sql order by date field issue

rubyonrails-talk
1

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?

2

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?

3

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

4

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

5

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

6

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

7

Anyone have any ideas?

8

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

9

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")