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
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