get all products of category and child categories (rails, awesome_nested_set)

Question

having an e-commerce application under development i am trying to get my head around the following problem: I have my categories realized through the awesome_nested_set plugin. If I list my articles through selecting one category everything works fine, but for some links I want to show all the products of one category and the products of its child categories.

here is the controller code that works fine with only one category:

   # products_controller.rb
   def index
    if params[:category]
      @category = Category.find(params[:category])
      #@products = @category.product_list
      @products = @category.products
    else
      @category = false
      @products = Product.scoped
    end
    @products = @products.where("title like ?", "%" + params[:title] + "%") if params[:title]
    @products = @products.order("created_at).page(params[:page]).per( params[:per_page] ? params[:per_page] : 25)
    @categories = Category.all
  end

The line I commented out is a helper method I wrote myself in the category model which returns all products of the category and its child categories in an array.

It is defined as followed:

# app/models/category.rb
def product_list
  self_and_ancestors.to_a.collect! { |x| x.products }
end

Now when I uncomment this line and try to select one category my products controller code breaks with errors like

undefined method `order' for #<Array:0x1887c2c>

or

undefined method `page' for #<Array:0x1887c2c>

because I am using ordering and pagination and it can't order the arary anymore.

Any ideas how to get all the products in an ActiveRecord Relation element in my controller? thanks

UPDATE

so, when I use the following:

class Category < ActiveRecord::Base
    acts_as_nested_set

    attr_accessible :name, :description, :lft, :rgt, :parent_id   

    has_many :categorizations
    has_many :products, :through => :categorizations

    attr_accessor :product_list

    def branch_ids
      self_and_descendants.map(&:id).uniq 
    end

    def all_products
       Product.find(:all, :conditions => { :category_id => branch_ids } )
    end


end

and ask the controller for @category.all_products i get the following error:

Mysql::Error: Unknown column 'products.category_id' in 'where clause': SELECT `products`.* FROM `products` WHERE `products`.`category_id` IN (6, 8, 9)

How would I get all products with this constellation?

UPDATE 2

Ok, so I am going to start a bounty.

If I try:

def all_products Categorization.find(:all, :conditions => { :category_id => branch_ids } ) end

I get again undefined methodorder' for #` I need to know how I can get all the products of a many_to_many relation as an ActiveRecord relation.

UPDATE 3

I put the relevant code in a gist https://gist.github.com/1211231

Answer 1

The key with awesome_nested_set is to use a range in the lft column. Here's a code sample of how I do it with a direct association (category has_many articles)

  module Category
    extend ActiveSupport::Concern
    included do
      belongs_to :category
      scope :sorted, includes(:category).order("categories.lft, #{table_name}.position")
    end

    module ClassMethods
      def tree(category=nil) 
        return scoped unless category
        scoped.includes(:category).where([
          "categories.tree_id=1 AND categories.lft BETWEEN ? AND ?", 
          category.lft, category.rgt
        ])
      end
    end # ClassMethods
  end

Then somewhere in a controller

@category = Category.find_by_name("fruits")
@articles = Article.tree(@category) 

that will find all articles under the categories apples, oranges, bananas, etc etc. You should adapt that idea with a join on categorizations (but are you sure you need a many to many relationship here?)

Anyway I would try this :

class Product < AR
      has_many :categorizations

      def self.tree(category=nil) 
        return scoped unless category
        select("distinct(products.id), products.*").
        joins(:categorizations => :category).where([
          "categories.lft BETWEEN ? AND ?", category.lft, category.rgt
        ])
      end
end

Let me know if there's any gotcha