[MYSQL] A recursive query?

[eluser]Clooner[/eluser]
I have the following code to recursively get all the children of a certain category. But when I have a extensive category structure I soon end up with 20+ queries to do a thing that shouldn't be to complex.

Code:

function get_children($parent_id)
{
  $this->db->select('child');
  $this->db->where('parent', $parent_id);
  $query = $this->db->get('category_relations');
  if ($query->num_rows()>0)
  {
    $results=array();
    foreach ($query->result_array() as $row)
    {
      $results[]=$row;
      if ($sub_categories=$this->get_children($row['child']))
        $results=array_merge($results,$sub_categories);
    }
    return $results;
  } else
    return false;
}

Is there an easier way to do this? Preferably within one sql query

[eluser]m_ologin[/eluser]
I would probably add a 'children' column in your sql structure (varchar) that would hold the IDs of the children for that item:

Code:

ID| title | children
1 | "aze" | "2,4"
2 | "sdg" | "4"
3 | "erh" | "5,6"
4 | "zdh" | ""
5 | "shf" | ""
6 | "tyi" | ""

Then with only one query, find the recursive children of 1 :

Code:

SELECT * FROM t_... WHERE ID IN (SELECT children FROM t_... WHERE title="aze")

[eluser]pistolPete[/eluser]
I'd suggest having a look at SQL JOIN.

How does your database layout look like?
How many levels do you have in your structure? Could you give an example of the structure?

Btw: You have a syntax error in your function.

Code:

// is always TRUE because of the single '='
if ($sub_categories=$this->get_children($row['child']))

[eluser]Clooner[/eluser]
This is my structure. And the idea is that you will get unlimited sized category tree.

Code:

categories
----------------------------------------
|   ID   |   Title             |  Etc
----------------------------------------
|   1    |   Categorie title   |  ....
|   2    |   Categorie title   |  ....
|   3    |   Categorie title   |  ....
|   4    |   Categorie title   |  ....

category_relations (parent en child both point to the categories table)
----------------------------------------
|   ID   |   Parent       |   Child
----------------------------------------
|   1    |        1       |      4  
|   2    |        1       |      2
|   3    |        2       |      3
|   4    |        3       |      4

It is not that my code doesn't work but I soon end up with lots of queries and I want to avoid that

[eluser]Clooner[/eluser]
[quote author="pistolPete" date="1236960072"] Btw: You have a syntax error in your function.

Code:

// is always TRUE because of the single '='
if ($sub_categories=$this->get_children($row['child']))

[/quote]

The function get_children will return false if no children are found. (the single '=' is the way it should be)

[eluser]cwt137[/eluser]
@clooner - Google for 'adjacency list sql' or 'adjacency list model' or something like that. That might give you some tips.

[eluser]pistolPete[/eluser]
[quote author="clooner" date="1236960614"]This is my structure. And the idea is that you will get unlimited sized category tree.[/quote]

Have a look at this: http://ellislab.com/forums/viewreply/545521/

[eluser]cwt137[/eluser]
@pistolPete - I like the nested sets model best when storing hierarchical data inside a relational database, but the original poster would have to change his model. Maybe it is best for him to change his model since there is already a library for the job.

[eluser]Clooner[/eluser]
I'm looking into MPTT but in my understanding this is only for nicely ordered trees. What if it is a tree with the nodes mixed up? In my understanding it doesn't support this. Then it is better to cache results of my function and use these cached results, refreshing only once in a while.

[eluser]pistolPete[/eluser]

...this is only for nicely ordered trees. What if it is a tree with the nodes mixed up?

Could you give an example of
- what you mean by "mixed up" and
- what kind of data you are trying to categorize ?