Hierarchical category - ability to include child items (in SQL?)

Hi there,

I have a list of items whereby one of the attributes is a category, for example:

-- Cars

---- BMW

------ 2011

------ 2010

---- Toyota

------ 2011

------ 2010

---- Hyundai

------ 2011

------ 2010

-- Boats

---- Big Ones

---- Small Ones

Basically I want to be able to either have an item selected at the top level (ie. Cars or Boats), or at a more specific level (ie BMW, or Small Boats).

However the problem I am facing is that in Yii if an item has a “category” then it only appears in searches for that individual category, where as what I want to do is essentially drill down the tree to see if it’s a child of the currently selected category too.

I’ve read quite a few ways of doing this:

  • Storing all the categories it’s related to in a join table – Messy!

  • Using a "path" as the category, rather than a fixed ID and then use a "starts with" search to determine the depth/scope of the search.

I would ideally like something at the SQL level to minimise load on the application… I’ve seen a lot of applications that have the concept of an “value IN TREE WITH ROOT” search option… I guess that is what I’m after.

Can anyone provide insight as to how I may achieve this in an efficient way? :)