thr3ads.net - Rails - Categorising records (and then finding them by category) [Feb 2007]

If this information is useful, please help other people find it:
Share via:

naptrel

2007-Feb-07 13:53 UTC

Categorising records (and then finding them by category)

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

timfischbach

2007-Feb-07 14:15 UTC

head link

Re: Categorising records (and then finding them by category)

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

On 7 Feb., 14:53, naptrel
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> 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
>
> --
> Posted viahttp://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

naptrel

2007-Feb-07 14:27 UTC

head link

Re: Categorising records (and then finding them by category)

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 Development 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. Ive
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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

timfischbach

2007-Feb-07 14:42 UTC

head link

Re: Categorising records (and then finding them by category)

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

On 7 Feb., 15:27, naptrel
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> 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 Development 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
>
> --
> Posted viahttp://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Thorsten L

2007-Feb-07 15:03 UTC

head link

Re: Categorising records (and then finding them by category)

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| %>
   <%= "<h1>#{Cateory c.name}</h1>" %>
  <ul>
   c.items.each do |i|
      <%= "<li>#{i.name}</li>"
   end
  <%=</ul>%>
end

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

On 7 Feb., 15:15, "timfischbach"
<i...-UXIzdl3yWSfeWPZRMiJ+Cw@public.gmane.org>
wrote:> 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
>
> On 7 Feb., 14:53, naptrel
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
>
>
>
> > 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
>
> > --
> > Posted viahttp://www.ruby-forum.com/.- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Rails - Feb 2007 - Categorising records (and then finding them by category)

Categorising records (and then finding them by category)

Re: Categorising records (and then finding them by category)

Re: Categorising records (and then finding them by category)

Re: Categorising records (and then finding them by category)

Re: Categorising records (and then finding them by category)