On Sat, Mar 5, 2011 at 11:57 AM, Brad Phelan <[email protected]>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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
