AREL probelm with postgress

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

http://xtargets.com

On Sat, Mar 5, 2011 at 11:57 AM, Brad P.
[email protected]wrote:

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

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


Brad P.
http://xtargets.com

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

On Sun, Mar 6, 2011 at 12:55 PM, Brad P.
[email protected]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.

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 P.
http://xtargets.com

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs