thr3ads.net - Rails - Need help expressing a double filtered selection in a pagination statement [Apr 2008]

If this information is useful, please help other people find it:
Share via:

Carson

2008-Apr-28 18:33 UTC

Need help expressing a double filtered selection in a pagination statement

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Roger Pack

2008-Apr-28 23:33 UTC

head link

Re: Need help expressing a double filtered selection in a pagination statement

use find_by_sql and use the SQL that you''d anticipate using [including
the joins].?

On Mon, Apr 28, 2008 at 12:33 PM, Carson
<E.CarsonCampbell-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
>  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
>
>
>  >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Carson

2008-Apr-29 14:23 UTC

head link

Re: Need help expressing a double filtered selection in a pagination statement

Thanks!

Carson

On Apr 28, 6:33 pm, "Roger Pack"
<rogerpack2...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> use find_by_sql and use the SQL that you''d anticipate using
[including
> the joins].?
>
>
>
> On Mon, Apr 28, 2008 at 12:33 PM, Carson
<E.CarsonCampb...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> >  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- Hide quoted text -
>
> - Show quoted text ---~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Rails - Apr 2008 - Need help expressing a double filtered selection in a pagination statement

Need help expressing a double filtered selection in a pagination statement

Re: Need help expressing a double filtered selection in a pagination statement

Re: Need help expressing a double filtered selection in a pagination statement