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