在 Rails 3 中重构 activerecord 方法

Question

这个事件方法可以重构和简化吗？

class Manager < User
  has_and_belongs_to_many :customers

  def events
    Event.joins(:customers => :managers).where(:users => { :id => self }).select("DISTINCT(events.id), events.description, events.created_at")
  end

end

我希望我可以在我当前拥有的 Manager 实例之上构建查询，但似乎无法做到这一点。我尝试了以下操作，但出现错误

def events
  customers.joins(:events).select("DISTINCT(events.id), events.description, events.created_at")
end

，

current_user.events

但这会导致 MySQL 错误：

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(events.id), events.description, events.created_at FROM `customers` INNER' at line 1: SELECT `customers`.*, DISTINCT(events.id), events.description, events.created_at FROM `customers` INNER JOIN `customers_events` ON `customers_events`.`customer_id` = `customers`.`id` INNER JOIN `events` ON `events`.`id` = `customers_events`.`event_id` INNER JOIN `customers_managers` ON `customers`.`id` = `customers_managers`.`customer_id` WHERE `customers_managers`.`manager_id` = 27 ORDER BY created_at DESC

Answer 1

根据MySQL手册，DISTINCT关键字必须出现在select_expr:

SELECT foo.*, DISTINCT(foo.id) 

会给你这个错误。

SELECT distinct(foo.id), foo.*

会起作用的。试试这个：

customers.select("DISTINCT(events.id), events.description, events.created_at").joins(:events)

但是，如果您想做的只是获取每个用户的唯一事件（我认为这就是 DISTINCT 的用途），您可以使用

current_user.events.group("events.id")