# Categorising records (and then finding them by category) **URL:** \ **Category:** Rails\ **Created:** [February 7, 2007, 2:53pm UTC](https://www.ruby-forum.com/t/categorising-records-and-then-finding-them-by-category/84754 "2007-02-07T14:53:13Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![naptrel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/e19adc/32.png) [@naptrel](https://www.ruby-forum.com/u/naptrel)\ **Post date:** [February 7, 2007, 2:53pm UTC](https://www.ruby-forum.com/t/categorising-records-and-then-finding-them-by-category/84754/1 "2007-02-07T14:53:13Z")
Dear all, The following is a dummy scenario to explain the simple thing I need help with without burdening people with the messy details of my reality: I have 2 tables: Items and Categories. Items are things you can buy and Categories are, as you might suspect, categories used to group similar items together. The Categories table “acts as tree” and, as per the standard Rails system, contains a parent\_id field which relates a category to a parent category. For example, my category tree might look something like: |-- Musical Instrument | | | |-- Brass | | | | | |-- French Horn | | |-- Trombone | | |-- Trumpet | | |-- Tuba | | | |-- String | | | | | |-- Cello | | |-- Double Bass | | |-- Viola | | |-- Violin | | | |-- Woodwind | | | |-- Bassoon | |-- Clarinet | |-- Flute | |-- Oboe | |-- Sheet Music | |-- Classical |-- Jazz |-- Pop Each record in the Items table “belongs to” a category node (e.g. “Clarinet”, “Violin”, “Jazz”), not a branch (i.e. not “Woodwind”, “String”, “Sheet Music” etc). So, if I have a Boosey & Hawkes clarinet for sale, it is tied to the “Clarinet” category (it has a foreign key, “category\_id”, that contains the ID of the “Clarinet” category,). My question is this: how can I, in a single SQL query, find all the Items for sale that belong to the “Woodwind” category? Or, How can I list all the “Musical Instruments” for sale? I would dearly love to cut down the communications between Rails and my database, so I really don’t want to iterate through all the “Woodwind” subcategories finding the records which match that request. Good SQLers can do things much more elegantly than that. Sadly I’m not a good SQLer. Besides, once I’ve mastered this, I want to extend this so that an item can belong to more than one category (a standard many-to-many relationship using an intermediary table)—without an efficient SQL request this will become very ugly. Thanks in advance, Nat ---
**Author:** ![naptrel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/e19adc/32.png) [@naptrel](https://www.ruby-forum.com/u/naptrel)\ **Post date:** [February 7, 2007, 3:15pm UTC](https://www.ruby-forum.com/t/categorising-records-and-then-finding-them-by-category/84754/2 "2007-02-07T15:15:42Z")
Hi, have a look at ActiveRecord::Acts::NestedSet. It supports finding all (indirect) children of a node. To realize the many-to-many relation between items and categories I’d suggest introducing a new model (something like ‘Categorization’), which takes the role of an item in your current design. Then every categorization can belong to one item, and items have many categorizations. Hope that helps, Tim ---
**Author:** ![naptrel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/e19adc/32.png) [@naptrel](https://www.ruby-forum.com/u/naptrel)\ **Post date:** [February 7, 2007, 3:27pm UTC](https://www.ruby-forum.com/t/categorising-records-and-then-finding-them-by-category/84754/3 "2007-02-07T15:27:26Z")
Hi Tim, Thanks for the reply and the pointer towards nested sets. I was completely unaware of its existence having not noticed the footnote in “Agile Web D. with Rails” (2nd ed) that mentions it: “Rails ships with three acts as extensions: acts\_as\_list, acts\_as\_tree, and acts\_as\_nested\_set. I’ve chosen to document just the first two of these; as this book was being finalized, the nested set variant still has some serious problems that prevent us from verifying its use with working code.” Does anyone know what these “serious problems” are, or have they been fixed? Nat ---
**Author:** ![naptrel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/e19adc/32.png) [@naptrel](https://www.ruby-forum.com/u/naptrel)\ **Post date:** [February 7, 2007, 3:43pm UTC](https://www.ruby-forum.com/t/categorising-records-and-then-finding-them-by-category/84754/4 "2007-02-07T15:43:49Z")
I have not used NestedSet in any of my projects yet. The only problem I am aware of right now is, that inserting new nodes can be quite expensive, since all nodes of the tree are numbered in a depth-first- search manner. But this might not be a problem, if the tree is only altered once in a while (which could be the case in your scenario). Maybe there are further drawbacks, that I am ignorant of. Tim ---
**Author:** ![naptrel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/e19adc/32.png) [@naptrel](https://www.ruby-forum.com/u/naptrel)\ **Post date:** [February 7, 2007, 4:04pm UTC](https://www.ruby-forum.com/t/categorising-records-and-then-finding-them-by-category/84754/5 "2007-02-07T16:04:53Z")
no idea how to do it with 1 Query, and i’m really not yet really comfortable with the whole Ruby lang itself, but this could work with 2 queries if you use act\_as\_nested\_set instead of atcs\_as\_tree: add this method to your model Category Model: def direct\_children\_and\_their\_items self.class.base\_class.find(:all, :conditions =\> “#{scope\_condition} and #{parent\_column} = #{self.id}”, :include =\> items) end basically this is the direct\_children() moethod of acts\_as\_nested\_set, but with “:include =\> :items” added. then you can do: #in your controller: @cats\_and\_items = Category.find(params[:id]).direct\_children\_and\_their\_items # this would be 2 querys. 1 to find your category objects, one to find all subcategories and their children #view \<% @cats\_and\_items.each do |c| %\> \<%= “ # #{Cateory c.name} ” %\> c.items.each do |i| \<%= "- #{i.name} " end \<%= %\> end no Idea if this really works, just an idea. maybe it helps.