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

**URL:** <https://www.ruby-forum.com/t/need-help-expressing-a-double-filtered-selection-in-a-pagination-statement/136093>\
**Category:** Rails\
**Created:** [April 28, 2008, 9:37pm UTC](https://www.ruby-forum.com/t/need-help-expressing-a-double-filtered-selection-in-a-pagination-statement/136093 "2008-04-28T21:37:29Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Carson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/e9bcb4/32.png) [@Carson](https://www.ruby-forum.com/u/Carson)\
**Post date:** [April 28, 2008, 9:37pm UTC](https://www.ruby-forum.com/t/need-help-expressing-a-double-filtered-selection-in-a-pagination-statement/136093/1 "2008-04-28T21:37:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Carson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/e9bcb4/32.png) [@Carson](https://www.ruby-forum.com/u/Carson)\
**Post date:** [April 29, 2008, 1:34am UTC](https://www.ruby-forum.com/t/need-help-expressing-a-double-filtered-selection-in-a-pagination-statement/136093/2 "2008-04-29T01:34:25Z")

</div>

use find\_by\_sql and use the SQL that you’d anticipate using [including  
the joins].?

---

<div class="post-metadata">

**Author:** ![Carson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/e9bcb4/32.png) [@Carson](https://www.ruby-forum.com/u/Carson)\
**Post date:** [April 29, 2008, 4:24pm UTC](https://www.ruby-forum.com/t/need-help-expressing-a-double-filtered-selection-in-a-pagination-statement/136093/3 "2008-04-29T16:24:56Z")

</div>

Thanks!

Carson