Rails - Feb 2005 - find_all with a list of values

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