Rails - Jun 2008 - 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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Thanks for the idea.  I tried and it looks exactly the same....
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.....
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Anyone have any ideas?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---