ruby-on-rails - 查询适用于 MySQL 但不适用于 Postgresql

Question

我想知道如何为 postgress 数据库编写这个 ruby​​ on rails 查询？它通过 mysql 工作，但在 postgres 上它返回一个错误:

RUBY ON RAILS 查询

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)

错误

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

投票模型

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)

原始生成的 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)

谢谢

Answer 1

解决方案

问题出在范围内的 JOIN ON 上。这适用于两个数据库:mysql 和 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 查询

  # 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)