Categorising records (and then finding them by category)

naptrel · February 7, 2007, 2:53pm

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:

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

naptrel · February 7, 2007, 3:15pm

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

naptrel · February 7, 2007, 3:27pm

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

naptrel · February 7, 2007, 3:43pm

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

naptrel · February 7, 2007, 4:04pm

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}

” %>

#{i.name}

%> end

no Idea if this really works, just an idea. maybe it helps.