mysql - Rails复杂的SQL

我有几个复杂的SQL，需要将其转换为ActiveRecord查询。请帮我：

我的模特：

class Product < ActiveRecord::Base
  belongs_to :watch, :counter_cache => true
end

class Watch < ActiveRecord::Base
  belongs_to :category
  has_many :products
end

class Category < ActiveRecord::Base

  has_ancestry :cache_depth => true, :depth_cache_column => :depth

  has_many :watches, :dependent => :destroy
  has_many :products, :through => :watches

end

因此，类别有两个层次的渊源，根源是品牌，孩子是意甲。
我的SQL如下：

scope :by_make, lambda { |make_name| Product.find_by_sql("
    SELECT p.* FROM products p INNER JOIN watches w ON p.watch_id = w.id
      INNER JOIN categories series ON w.category_id = series.id
      INNER JOIN categories makes ON series.ancestry = makes.id
      WHERE makes.name LIKE '%#{make_name}%'
    ") unless make_name.blank? }

 scope :by_series, lambda { |series_name| Product.find_by_sql("
      SELECT p.* FROM products p INNER JOIN watches w ON p.watch_id = w.id
        INNER JOIN categories series ON w.category_id = series.id
        WHERE series.name LIKE '%#{series_name}%'
      ") unless series_name.blank? }

请帮助将它们转换为ActiveRecord查询，因为不要在查询末尾获取数组非常重要，谢谢！

最佳答案

最简单的解决方案是在where的开头添加find_by_sql过滤器，如下所示：

  scope :by_make, lambda { |make_name| where(:watch_id => Watch.find_by_sql("
    SELECT w.* FROM watches w
      INNER JOIN categories series ON w.category_id = series.id
      INNER JOIN categories makes ON series.ancestry = makes.id
      WHERE makes.name LIKE '%#{make_name}%'
    ")) unless make_name.blank? }

  scope :by_series, lambda { |series_name| where(:watch_id => Watch.find_by_sql("
      SELECT w.* FROM watches w
        INNER JOIN categories series ON w.category_id = series.id
        WHERE series.name LIKE '%#{series_name}%'
      ")) unless series_name.blank? }

应该返回AR集合。

关于mysql - Rails复杂的SQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7093727/