Hi all,
I am testing locally with MySQL and deploying to heroku ( postgress ) The
following query seems to
generate a PGError.
SELECT COUNT(*) FROM "events" INNER JOIN "events"
"events_2" WHERE
("events"."schedule_id" = 1 AND
"events"."start_at" =
"events_2"."start_at"
AND "events_2"."schedule_id" IN (3))
It is generated via an AREL builder by calling
coliding_events.count
where coliding_events is defined as
----------------------
app/models/schedule.rb
----------------------
43 def coliding_events
44 cs = []
45
46 # Id''s of schedules with overlapping times of day
47 pcsids = possibly_coliding_schedules.map &:id
48
49 if pcsids.size == 0
50 return []
51 end
52
53 e0 = Event.arel_table
54 e1 = Event.arel_table.alias # because we do a self join
55
56 # compare self events
57 j0 = e0[:schedule_id].eq(id)
58
59 # and find events on the same dates
60 j1 = e0[:start_at].eq(e1[:start_at])
61
62 # whose times of day are overlapping
63 j2 = e1[:schedule_id].in(pcsids)
64
65
66 Event.joins(e1).where(
67 j0.and(j1).and(j2)
68 )
69
70 end
The error I get from postgress is
2011-03-05T10:26:21-08:00 app[web.1]: ActionView::Template::Error (PGError:
ERROR: syntax error at or near "WHERE"
2011-03-05T10:26:21-08:00 app[web.1]: LINE 1: ...(*) FROM "events"
INNER
JOIN "events" "events_2" WHERE ("ev...
I don''t really have any experience with postgress but the query looks
sounds
to me.
Regards
Brad Phelan
--
http://xtargets.com
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On Sat, Mar 5, 2011 at 11:57 AM, Brad Phelan <bradphelan-c/WG92ZdtxxWk0Htik3J/w@public.gmane.org>wrote:> Hi all, > > I am testing locally with MySQL and deploying to heroku ( postgress ) The > following query seems to > generate a PGError. > > SELECT COUNT(*) FROM "events" INNER JOIN "events" "events_2" WHERE > ("events"."schedule_id" = 1 AND "events"."start_at" = "events_2"."start_at" > AND "events_2"."schedule_id" IN (3)) > >Brad, I would recommend trying the following if you haven''t already done so: 1) Writing the code without accessing the underlying ARel structure. 2) Running the raw query within a PG client. I haven''t played enough with ARel but I would suggest trying (1) and (2) above. Futhermore, by doing (1), you can see what SQL is being generated by using the method to_sql and compare it against (2). Good luck, -Conrad> It is generated via an AREL builder by calling > > coliding_events.count > > where coliding_events is defined as > > ---------------------- > app/models/schedule.rb > ---------------------- > 43 def coliding_events > 44 cs = [] > 45 > 46 # Id''s of schedules with overlapping times of day > 47 pcsids = possibly_coliding_schedules.map &:id > 48 > 49 if pcsids.size == 0 > 50 return [] > 51 end > 52 > 53 e0 = Event.arel_table > 54 e1 = Event.arel_table.alias # because we do a self join > 55 > 56 # compare self events > 57 j0 = e0[:schedule_id].eq(id) > 58 > 59 # and find events on the same dates > 60 j1 = e0[:start_at].eq(e1[:start_at]) > 61 > 62 # whose times of day are overlapping > 63 j2 = e1[:schedule_id].in(pcsids) > 64 > 65 > 66 Event.joins(e1).where( > 67 j0.and(j1).and(j2) > 68 ) > 69 > 70 end > > The error I get from postgress is > > 2011-03-05T10:26:21-08:00 app[web.1]: ActionView::Template::Error (PGError: > ERROR: syntax error at or near "WHERE" > 2011-03-05T10:26:21-08:00 app[web.1]: LINE 1: ...(*) FROM "events" INNER > JOIN "events" "events_2" WHERE ("ev... > > I don''t really have any experience with postgress but the query looks > sounds to me. > > Regards > > Brad Phelan > -- > http://xtargets.com > > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. >-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
I''ve installed the postgres db under rails osx and I can reproduce the bug locally. Makes sense to develop locally with postgres if deploying on heroku :) -- Brad Phelan http://xtargets.com -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Seems that
SELECT COUNT(*) FROM "events" INNER JOIN "events"
"events_2" WHERE
("events"."schedule_id" = 1 AND
"events"."start_at" =
"events_2"."start_at"
AND "events_2"."schedule_id" IN (3));
is bad but this is ok
SELECT COUNT(*) FROM "events" INNER JOIN "events"
"events_2" ON
("events"."schedule_id" = 1 AND
"events"."start_at" =
"events_2"."start_at"
AND "events_2"."schedule_id" IN (3));
Perhaps in postgres using a JOIN always requires an ON clause though in
MySQL this is not always the case.
Any ideas?
B
--
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On Sun, Mar 6, 2011 at 12:55 PM, Brad Phelan <bradphelan-c/WG92ZdtxxWk0Htik3J/w@public.gmane.org>wrote:> Perhaps in postgres using a JOIN always requires an ON clause though in > MySQL this is not always the case. >It looks like MySQL is forgiving the missing ON in your first expression thus executing your WHERE clause as a normal WHERE condition of the JOIN. PostgreSQL is not forgiving you the missing ON statement in the join. It''s looking for that ON because it knows you''re running a JOIN and the SQL syntax states you need an ON. Not all databases follow or enforce the same SQL rules so SQL code written for one will not port nicely over to the other all the time. B. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
I fixed the problem eventually but it was not obvious how. ---------------------- app/models/schedule.rb ---------------------- 39 # -- 40 # Return all coliding 41 # events 42 # 43 def coliding_events 44 cs = [] 45 46 # Id''s of schedules with overlapping times of day 47 pcsids = possibly_coliding_schedules.map &:id 48 49 if pcsids.size == 0 50 return [] 51 end 52 53 e0 = Event.arel_table 54 e1 = Event.arel_table.alias # because we do a self join 55 56 # compare self events 57 j0 = e0[:schedule_id].eq(id) 58 59 # and find events on the same dates 60 j1 = e0[:start_at].eq(e1[:start_at]) 61 62 # whose times of day are overlapping 63 j2 = e1[:schedule_id].in(pcsids) 64 65 66 q = e0.join(e1).on(j1) 67 Event.joins(q.join_sql).where(j0.and(j2)) 68 end You need to use the join_sql method on the Arel object and pass that to the active record joins method. I also explicity used an ''on'' clause. At the moment the documentation seems pretty thin on using Arel directly within active record. -- Brad Phelan http://xtargets.com -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.