问题描述:

I have couple of complex SQL and need to convert it into an ActiveRecord query. Please help me:

My models:

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

So Category have ancestry with two level deep, root is make and children is serie.

My SQLs are following:

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? }

Please help to convert those into ActiveRecord queries, because it's very important not to get array on the end of query, thanks!

网友答案:

The most simple solution is just add where filter at the start of find_by_sql, something like that:

  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? } 

Should return AR collection.

相关阅读:
Top