mysql - Rails 复杂 SQL

Question

我有几个复杂的 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

所以类别有两层深度的祖先，根是 make，子级是 serie。我的 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 查询，因为不要在查询末尾获取数组非常重要，谢谢!

Answer 1

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

  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 集合。