Four tables are involved: Departments, Categories, Products and ProductCategories.
Department has_many :categories Category has_and_belongs_to_many :products Products has_and_belongs_to_many :categories
In a controller pagination statement (I’m using classic_pagination), I need to screen products for display using a double filter. Here’s a generic example:
There are many Departments (department_id 1, 2, 3, etc.). Each Department can have many Categories (category_id 1, 2, 3, etc.).
Each Category can have many Products (product_id 1, 2, 3, etc.). Each Category record has an integer attribute labeled “department_id” (1, 2, 3, etc.).
Each Product can belong to many Categories. Each Product record has a boolean attribute labeled “on_department_promotion” (true, false).
The ProductCategories table associates “product_id” to “category_id”.
Upon menu selection of a Department, I want to display all Products associated with Categories associated with that Department and indicating “on_department_promotion” = "true".
In SQL syntax, I would express the filter like:
SELECT DISTINCT [product attributes] FROM Products INNER JOIN ProductCategories ON Product.product_id = ProductCategories.product_id INNER JOIN Category ON ProductCategories.category_id = Category.category_id
SELECT [product attributes] FROM Products WHERE Product.on_department_promotion = “true” AND Category.department_id = Department.department_id
So far my controller statement looks like this:
def department @departments = Department.find(:all) @categories = Category.find_all_by_department_id(params[:id]) @department = Department.find_by_department_id(params[:id]) @product_pages, @products = paginate(:products, per_page => 6, :conditions => ["on_department_promotion = ?", true] ) end
But as you can see, I’m a long way from being able to invoke a dual filter.
Any suggestions?
Carson