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.
Brad, I would recommend trying the following if you haven’t already done
so:
Writing the code without accessing the underlying ARel structure.
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).
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.
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.
