0

In the query below there is a field called main which is used to differentiate the categories. So, there are common categories and main categories and both can be found on a tree. The main field is just a type and there's nothing with the tree structure, I mean, it's like a status field. 

  SELECT `c`.*, 
           (count(p.id)-1) AS `depth` 
      FROM `categories` AS `c` 
  CROSS JOIN `categories` AS `p` 
     WHERE (c.lft BETWEEN p.lft AND p.rht) 
       AND (c.root_id =p.root_id) 
       AND (p.main =1) 
  GROUP BY `c`.`id` 
  ORDER BY `c`.`root_id` ASC, `c`.`lft` ASC

There is a where clausule which specify that the parent category need to be a main category. Also, sometimes I need to do a select where the parent category is a common category p.main =0.

The depth is the position of the element in the tree. So if a category is a one level children of another, the depth will be 1, if two levels, the depth will be 2.

My problem is that when I do the select above, if there are children marked as common category on a tree where the father is a main category (selecting p.main =1) the common categories depth is always 0.

In other words, the select works properly, if I select all categories that has the top parent marked as main, it will display the tree with all categories including children categories marked with main=0. But in this case, the depth is always 0

See the results:

alt text

The category 1423 is a child of 27 and is not a main category, but 27 is, so depth is 0, but need to be 1. The category 276 is a child of 64 and both are a main categories, so it has the right depth.

How can I change this query so that the depth field work as expected?

Reference here: How to generate a tree view from this result set based on Tree Traversal Algorithm?

Community
  • 1
  • 1
Keyne Viana
  • 6,194
  • 2
  • 24
  • 55
  • The words 'nested set' would help here. – ijw Dec 06 '10 at 20:04
  • Do you want 'depth' to equal the number oF ancestors this element has? Do you want this to run for only nodes that have an ancestor that is a 'main' node? Do you want it to only count ancestors that are 'main'? Because it's hard to tell from your question. – ijw Dec 09 '10 at 16:15
  • @ijw See my edit and tell me if it's clear now – Keyne Viana Dec 09 '10 at 19:07

1 Answers1

0

You actually say you're looking for this count but only for nodes what are children of a main parent. If that's what you truly want then your query is more wrong than just fixing that - the table you call 'p' and seem to think is a 'parent' table is actually an 'ancestor' table - similarly 'c' is not child but 'descendant'. You end up counting main-flagged ancestors of all nodes in the tree.

ijw
  • 4,376
  • 3
  • 25
  • 29
  • I didn't get the point. Everything work prolerly when all the categories involved are main or common. Can you clarify? There are a cross join with group by. So, the count works even if the category has no childrens. And Yes, parent is the ancestor and not a main parent. The main filed has another purpose. It's like a type. – Keyne Viana Dec 06 '10 at 21:06