# Named scopee on an association table **URL:** \ **Category:** Rails\ **Created:** [July 13, 2010, 10:26am UTC](https://www.ruby-forum.com/t/named-scopee-on-an-association-table/191477 "2010-07-13T10:26:09Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![gregm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/bc79bd/32.png) [@gregm](https://www.ruby-forum.com/u/gregm)\ **Post date:** [July 13, 2010, 10:26am UTC](https://www.ruby-forum.com/t/named-scopee-on-an-association-table/191477/1 "2010-07-13T10:26:09Z")
Hi, I have a Category and a Model product with an association table categories\_products. I want a named that returns the categories with products. Here is what I’ve done (in the Category model): named\_scope :with\_products, lambda { { :joins =\> “inner join categories\_products on (categories.id = categories\_products.category\_id)” ``` }} ``` The problem with this named\_scopes is that it returns X number of the time the same category. (X is the number of products that the category as) What in my named scope I have to add to avoid this? Greg ---
**Author:** ![gregm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/bc79bd/32.png) [@gregm](https://www.ruby-forum.com/u/gregm)\ **Post date:** [July 13, 2010, 11:36am UTC](https://www.ruby-forum.com/t/named-scopee-on-an-association-table/191477/2 "2010-07-13T11:36:59Z")
Colin L. wrote: > On 13 July 2010 09:26, Greg Ma [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > � � �:joins =\> "inner join categories\_products on (categories.id = > > Assuming that you have defined has\_and\_belongs\_to relationships > between the classes (or has\_many through if that is what you prefer) > then if you have a category you can use @category.products to give you > and array of products for that category, there is no need for a named > scope. Such is the magic of Rails. > > Colin Yes I know that but I’m not trying to get Products but Categories with products ---
**Author:** ![gregm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/bc79bd/32.png) [@gregm](https://www.ruby-forum.com/u/gregm)\ **Post date:** [July 13, 2010, 11:01am UTC](https://www.ruby-forum.com/t/named-scopee-on-an-association-table/191477/3 "2010-07-13T11:01:09Z")
On 13 July 2010 09:26, Greg Ma [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > ``` > :joins => "inner join categories_products on (categories.id = > > ``` Assuming that you have defined has\_and\_belongs\_to relationships between the classes (or has\_many through if that is what you prefer) then if you have a category you can use @category.products to give you and array of products for that category, there is no need for a named scope. Such is the magic of Rails. Colin ---
**Author:** ![gregm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/bc79bd/32.png) [@gregm](https://www.ruby-forum.com/u/gregm)\ **Post date:** [July 13, 2010, 12:31pm UTC](https://www.ruby-forum.com/t/named-scopee-on-an-association-table/191477/4 "2010-07-13T12:31:19Z")
On 13 July 2010 10:36, Greg Ma [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > Colin > > Yes I know that but I’m not trying to get Products but Categories with > products I thought you meant “categories (along) with (their) products”. Do you mean that you want all the categories that have at least one product, ie where category.products.count \> 0? Colin ---
**Author:** ![gregm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/bc79bd/32.png) [@gregm](https://www.ruby-forum.com/u/gregm)\ **Post date:** [July 13, 2010, 2:03pm UTC](https://www.ruby-forum.com/t/named-scopee-on-an-association-table/191477/5 "2010-07-13T14:03:59Z")
Colin L. wrote: > On 13 July 2010 10:36, Greg Ma [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > > Colin > > > > Yes I know that but I’m not trying to get Products but Categories with > > products > > I thought you meant “categories (along) with (their) products”. > Do you mean that you want all the categories that have at least one > product, ie where category.products.count \> 0? > > Colin That doesnt work either, that tells you if the current category has more than one products. And I want all the categories which has at least one product. ---
**Author:** ![gregm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/bc79bd/32.png) [@gregm](https://www.ruby-forum.com/u/gregm)\ **Post date:** [July 13, 2010, 2:07pm UTC](https://www.ruby-forum.com/t/named-scopee-on-an-association-table/191477/6 "2010-07-13T14:07:26Z")
Greg Ma wrote: > Colin L. wrote: > > > On 13 July 2010 10:36, Greg Ma [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > > > > Colin > > > > > > Yes I know that but I’m not trying to get Products but Categories with > > > products > > > > I thought you meant “categories (along) with (their) products”. > > Do you mean that you want all the categories that have at least one > > product, ie where category.products.count \> 0? > > > > Colin > > That doesnt work either, that tells you if the current category has more > than one products. > And I want all the categories which has at least one product. named\_scope :with\_products, lambda { { :select =\> “distinct categories.id”, :joins =\> “inner join categories\_products on (categories.id = categories\_products.category\_id)” }} It should be something like that, but this returns the categories id who has products, and I want the ActiveRecord object not the ids… ---
**Author:** ![gregm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/bc79bd/32.png) [@gregm](https://www.ruby-forum.com/u/gregm)\ **Post date:** [July 13, 2010, 2:47pm UTC](https://www.ruby-forum.com/t/named-scopee-on-an-association-table/191477/7 "2010-07-13T14:47:09Z")
On 13 July 2010 13:03, Greg Ma [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > Colin > > That doesnt work either, that tells you if the current category has more > than one products. > And I want all the categories which has at least one product. I did not say that is the solution, I asked whether you were trying to find all categories that meet that condition, to which I think the answer is yes. I suggest getting it working in a find first, then convert it to a named scope. You could use something like Category.find( :all, :include =\> :products ).select { |c| c.products.count \> 0 } Colin