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

[paron]

That's one reason I really like Bruce William's Paginator gem (
http://paginator.rubyforge.org/): it just paginates anything you give
it.

def index

@departments = Department.find(:all)
@categories = Category.find_all_by_department_id(params[:id])
@department = Department.find_by_department_id(params[:id])

product_count=
Product.find(:select=>'id', :conditions=>["on_department_promotion
= ?",true]

@pager = ::Paginator.new(product_count.length, 6) do |offset,
per_page|
Product.find(:all,:limit => per_page, :offset =>
offset, :conditions=>["on_department_promotion = ?",true])
end
@page = @pager.page(params[:page])

end

# In your view
<% @page.each do |product| %>
<%# Show something for each item %>
<% end %>
<%= @page.number %>
<%= link_to("Prev", products_url(:page => @page.prev.number)) if
@page.prev? %>
<%= link_to("Next", products_url(:page => @page.next.number)) if
@page.next? %>

Or something like that: I just munged his example code with your
problem statement.

Ron

[Ed Campbell]

Hey Ron:

 

I haven't heard about Paginator. But one of the problems I've had with Classic Pagination is working within the restrictions of the pagination statement. I'll check out Paginator because, so far, I haven't even addressed that problem.

 

Carson