Hi all, I have a intuitive category->subcategory->article relation. Categories have subcategories, subcategories belong to categories and have articles, articles belong to subcategories. I am trying to write an action that will show a specific category with all its subcategories and objects in those subcategories. def show @category = Category.find_by_name @params[''name''] @subcategories = Subcategory.find_all "category_id = #{@category.id}" @articles = Article.find_all ??? end Hw would I write the condition for coming up with all the articles in the selected subcategories (and thus the category to be displayed)? I could do it the hard way, iterating over @subcategories and collecting all ids in a temp array, then selecting all articles whose subcategory_id is in the temp array. I suspect there is an easier way though. :-) Many thanks in advance! -- Nicky
Hello, I would say that the most efficient way would be to add a instance method on the Category class like that : class Category [...] def all_articles Article.find_by_sql ["select a.* from articles a,subcategories s where a.subcategory_id=s.id and s.category_id=?",id] end [...] end Then in your controller : def show @category = Category.find_by_name @params[''name''] @articles = @category.all_articles end Hope this helps Michel On Fri, 25 Feb 2005 15:21:42 +0100, Nickolay Kolev <nmkolev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi all, > > I have a intuitive category->subcategory->article relation. Categories > have subcategories, subcategories belong to categories and have > articles, articles belong to subcategories. > > I am trying to write an action that will show a specific category with > all its subcategories and objects in those subcategories. > > def show > @category = Category.find_by_name @params[''name''] > @subcategories = Subcategory.find_all "category_id = #{@category.id}" > @articles = Article.find_all ??? > end > > Hw would I write the condition for coming up with all the articles in > the selected subcategories (and thus the category to be displayed)? > > I could do it the hard way, iterating over @subcategories and > collecting all ids in a temp array, then selecting all articles whose > subcategory_id is in the temp array. I suspect there is an easier way > though. :-) > > Many thanks in advance! > > -- Nicky > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On Fri, 25 Feb 2005 15:21:42 +0100, Nickolay Kolev <nmkolev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi all, > > I have a intuitive category->subcategory->article relation. Categories > have subcategories, subcategories belong to categories and have > articles, articles belong to subcategories. > > I am trying to write an action that will show a specific category with > all its subcategories and objects in those subcategories. > > def show > @category = Category.find_by_name @params[''name''] > @subcategories = Subcategory.find_all "category_id = #{@category.id}" > @articles = Article.find_all ??? > end > > Hw would I write the condition for coming up with all the articles in > the selected subcategories (and thus the category to be displayed)? > > I could do it the hard way, iterating over @subcategories and > collecting all ids in a temp array, then selecting all articles whose > subcategory_id is in the temp array. I suspect there is an easier way > though. :-)First, your subcategories query should use parameters. It''s not exactly required in your specific case, but it''s a good habit to get into IMO: @subcategories = Subcategory.find_all [''category_id = ?'', @category.id] As for the Articles problem, try this: # replaces subcategories w/ ? and joins to a comma separated string sql = ''subcategory_id IN (%s)'' % @subcategories.fill(''?'').join('','') @articles = Article.find_all([sql] + @subcategories.collect { |sc| sc.id }) Basically, this should give you a query like ''subcategory_id IN (?, ?, ?)'' and give it parameters for each ID. -- rick http://techno-weenie.net
I ended up writing it like this: class Article < ActiveRecord::Base belongs_to :subcategory def self.find_by_list_of_subcategories(subcategories) articles = Array.new subcategories.each {|sc| articles << sc.articles} return articles end end Is it fundamentally wrong or can I leave it like this? :-) I much rather prefer working with a class method like this one and avoid constructing custom SQL queries. -- Nicky
Michel''s method and my own execute one query, where yours executes 1 per sub category. On Fri, 25 Feb 2005 15:45:59 +0100, Nickolay Kolev <nmkolev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I ended up writing it like this: > > class Article < ActiveRecord::Base > > belongs_to :subcategory > > def self.find_by_list_of_subcategories(subcategories) > articles = Array.new > subcategories.each {|sc| articles << sc.articles} > return articles > end > > end > > Is it fundamentally wrong or can I leave it like this? :-) > I much rather prefer working with a class method like this one and > avoid constructing custom SQL queries. > > -- Nicky > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- rick http://techno-weenie.net
On Fri, Feb 25, 2005 at 03:45:59PM +0100, Nickolay Kolev wrote:> I ended up writing it like this: > > class Article < ActiveRecord::Base > > belongs_to :subcategory > > def self.find_by_list_of_subcategories(subcategories) > articles = Array.new > subcategories.each {|sc| articles << sc.articles} > return articles > end > > end > > Is it fundamentally wrong or can I leave it like this? :-) > I much rather prefer working with a class method like this one and > avoid constructing custom SQL queries.This could be something like: def self.find_by_list_of_subcategories(subcategories) subcategories.inject([]) {|ar, sc| ar.concat sc.articles} end You might end up wrapping find_by_sql if performance proves to be an issue. marcel -- Marcel Molina Jr. <marcel-WRrfy3IlpWYdnm+yROfE0A@public.gmane.org>
> Michel''s method and my own execute one query, where yours executes 1 > per sub category.Point taken.>> class Article < ActiveRecord::Base >> >> belongs_to :subcategory >> >> def self.find_by_list_of_subcategories(subcategories) >> articles = Array.new >> subcategories.each {|sc| articles << sc.articles}This sould have been subcategories.each {|sc| articles += sc.articles} of course.>> return articles >> end >> >> end-- Nicky