Query works on MySQL but not in Postgres SQL

Question

I would like to know how to write this ruby on rails query for a postgress database? It works via mysql, but on postgres it returns an error:

RUBY ON RAILS QUERY

response_array = Vote.
              by_leaderboard_date_range.
              joins(:user).
              joins(:event_poll_relationship).
              select('users.*, sum(points_earned) as total').
              group('user_id').
              order('total DESC').
              limit(20)

ERROR

2013-07-31T13:02:43.430333+00:00 app[web.3]: ActiveRecord::StatementInvalid (PG::Error: ERROR:  argument of JOIN/ON must be type boolean, not type integer
2013-07-31T13:02:43.430333+00:00 app[web.3]: LINE 1: ...ers"."id" = "votes"."user_id" LEFT JOIN events on events.esp...
2013-07-31T13:02:43.430333+00:00 app[web.3]:                                                              ^
2013-07-31T13:02:43.430333+00:00 app[web.3]: : SELECT  users.*, sum(points_earned) as total FROM "votes" INNER JOIN "event_poll_relationships" ON "event_poll_relationships"."id" = "votes"."event_poll_relationship_id" INNER JOIN "users" ON "users"."id" = "votes"."user_id" LEFT JOIN events on events.espn_event_id WHERE (events.espn_event_id = event_poll_relationships.espn_event_id AND events.start_date_time >= '2013-07-28 00:00:00' AND events.start_date_time <= '2013-12-31 00:00:00') GROUP BY user_id ORDER BY total DESC LIMIT 20):

VOTE MODEL

class Vote < ActiveRecord::Base

  scope :by_espn_event_id, lambda {|espn_event_id| joins(:event_poll_relationship).joins('LEFT JOIN events on events.espn_event_id').where('event_poll_relationships.espn_event_id = ? AND events.espn_event_id = event_poll_relationships.espn_event_id', espn_event_id) }
  scope :by_leaderboard_date_range, joins(:event_poll_relationship).joins('LEFT JOIN events on events.espn_event_id').where('events.espn_event_id = event_poll_relationships.espn_event_id AND events.start_date_time >= ? AND events.start_date_time <= ?',LeaderBoardRange.find(1).start_date.to_datetime,LeaderBoardRange.find(1).end_date.to_datetime)

RAW GENERATED SQL

SELECT  users.*, sum(points_earned) as total FROM "votes" INNER JOIN "event_poll_relationships" ON "event_poll_relationships"."id" = "votes"."event_poll_relationship_id" INNER JOIN "users" ON "users"."id" = "votes"."user_id" LEFT JOIN events on events.espn_event_id WHERE (events.espn_event_id = event_poll_relationships.espn_event_id AND events.start_date_time >= '2013-07-28 00:00:00' AND events.start_date_time <= '2013-12-31 00:00:00') GROUP BY user_id ORDER BY total DESC LIMIT 20)

Thanks

ipegasus · Accepted Answer · 2013-07-31 13:31:19Z

1

SOLUTION

The issue was on JOIN ON in the scope. This works on both dbs: mysql and postgres

  scope :by_espn_event_id, lambda {|espn_event_id| joins(:event_poll_relationship).joins('LEFT JOIN events ON (events.espn_event_id = event_poll_relationships.espn_event_id)').where('event_poll_relationships.espn_event_id = ?', espn_event_id) }
  scope :by_leaderboard_date_range, joins(:event_poll_relationship).joins('LEFT JOIN events ON (events.espn_event_id = event_poll_relationships.espn_event_id)').where('events.start_date_time >= ? AND events.start_date_time <= ?',LeaderBoardRange.find(1).start_date.to_datetime,LeaderBoardRange.find(1).end_date.to_datetime)

RUBY ON RAILS QUERY

  # calculate leaderboard by date range
  response_array = Vote.
      by_leaderboard_date_range.
      joins(:user).
      joins(:event_poll_relationship).
      select('users.*, sum(points_earned) as total').
      group('users.id, user_id').
      order('total DESC').
      limit(20)

answered Jul 31, 2013 at 13:31

ipegasus

15.3k9 gold badges57 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

Thanks for following up with a solution. In future please try to include your Rails and PostgreSQL versions as well. Props for properly including the generated SQL though.

Collectives™ on Stack Overflow

Query works on MySQL but not in Postgres SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related