mysql - 将 mysql 查询移动到模型

Question

我有以下运行良好的 mysql 查询。快速的背景故事，这按花费的总金额列出了我们的客户。

SELECT  SUM(price) AS money_spent, co.customer_id, cu.first_name, cu.last_name, cu.email_primary
FROM customer_order AS co 
JOIN customer AS cu ON (cu.customer_id = co.customer_id) 
GROUP BY co.customer_id
ORDER BY money_spent DESC

我尝试将其作为范围和子类移至客户模型，但两种方法都失败了。我对 Rails(以前的客户端)有些陌生，但我认为我很接近。希望你们能给我指出正确的方向。提前致谢!!!

def self.high_rollers  
  options = {
      :select   => "SUM(price) AS money_spent, co.customer_id, cu.first_name, cu.last_name, cu.email_primary",
      :from     => "customer_order AS co",
      :joins    => "customer AS cu ON (cu.customer_id = co.customer_id)",
      :group_by => "co.customer_id",
      :order    => "money_spent DESC"
            }   

将以下代码放入此 Controller 的索引操作中是可行的，但绝对不是正确的方法!

@high_bidders = Customer.find_by_sql(<<-SQL
       SELECT  SUM(price) AS money_spent, co.customer_id, cu.first_name, cu.last_name, cu.email_primary
       FROM customer_order AS co 
       JOIN customer AS cu ON (cu.customer_id = co.customer_id) 
       GROUP BY co.customer_id
       ORDER BY money_spent DESC

数据库 )

Answer 1

我假设您的模型名称是 Customer 和 CustomerOrder。您的表名不是传统的，我认为您已经在模型中使用 self.table_name 明确设置了表名。

class CustomerOrder < ActiveRecord::Base
  # ...
  belongs_to :customer

  scope :high_rollers, select("*, SUM(price) AS money_spent").
                       joins(:customer).
                       group(:customer_id).
                       order("money_spent DESC")
end