CODEWITHSUNDEEP
mysqlsqlruby-on-railsruby-on-rails-3activerecord
Petya petrov
Petya petrov

Reputation: 2223

Rails complex SQL

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!

Upvotes: 0

Views: 302

Answers (1)

Mikhail Nikalyukin
Mikhail Nikalyukin

Reputation: 11967

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.

Upvotes: 1

Related Questions