Need help expressing a double filtered selection in a pagination statement.

[Carson]

Four table 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.

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