Simplifying looping queries down to one query

Question

I have a table with a list of categories of different levels. I'm using the columns, category_id , category_title , category_level and category_parent_id

The following code is a part of my search script. For a given category in the search, I wish to obtain results not only belonging to the chosen category but also all child categories to this category (and the children of those etc). I've tackled it by looping through the children of the category and then the children of the children.

Is it possible to simplify this down to do one query? I want to get a string of all child categories separated by a comma so I can use it in a WHERE IN condition.

$categories = $this->input->get('category_id');
$subquery = $this->db->where('parent_id',$categories)->get('categories');
$subcats = $subquery->result();
foreach($subcats as $cat)    {
   $categories .= ','.$cat->category_id;
   $subsubquery = $this->db->where('parent_id',$cat->category_id)->get('categories');
   foreach($subsubquery->result() as $cat)    {
     $categories .= ','.$cat->category_id;
   }
 }

Output should be something like 2, 15, 45, 15, 41, 32

I am using active record queries btw (in CodeIgniter), incase the syntax confuses you. I will have at max about 50 categories ever stored in the table and max 3 levels.

Perhaps this question [recursive categories with a single query](http://stackoverflow.com/questions/3116330/recursive-categories-with-a-single-query) might help — Conrad Frix, Sep 02 '11 at 16:25

score 1 · Answer 1 · answered Sep 02 '11 at 16:34

If you want to be able to run one query and get all the results, you might want to think about changing your table structure to something like this:

http://www.sitepoint.com/hierarchical-data-database-2/

It is a little complex when adding or removing rows, but will make your searches very fast.

score 0 · Accepted Answer · answered Sep 02 '11 at 23:21

If speed is not an issue, the nested loops you have should work fine. But if you want to avoid recursion or nested loops for getting the categories and subcategories, you can create a new column that maps out the lineage of a category.

Example:

1
- 2
  - 3
  - 4
    - 5
- 6
  - 7
    - 8

For category 5, the value of the lineage would be

/1/2/4/5/

For category 3, the value of the lineage would be

/1/2/3/

When you want to query for category 2 and all its subcategories, your code should look like this:

$this->db->like('lineage', '/2/'); //Equivalent to WHERE lineage LIKE '%/2/%'

Inserting a new category can be a pain because you need another code to generate lineage. I wouldn't recommend this solution if the user can reassign the parent of a category because you need to regenerate the lineage for all affected children.

Simplifying looping queries down to one query

2 Answers2