Msg#: 4505378 posted 10:10 am on Oct 8, 2012 (gmt 0)
This is my DB structure:
cat_ID| cat_name | cat_nicename | lft | rgt _________________________________________________ 287 | Wallpapers | wallpapers | 61 | 62 1656 | Gadget | gadget | 63 | 64 1958 | Sms | sms | 65 | 66 2901 | fun | fun | 67 | 68 4419 | Joke | joke | 69 | 70 4775 | health | health | 71 | 72 5098 | Mobile | mobile | 73 | 90 5100 | TV | tv | 91 | 92 5101 | Main | main | 93 | 94 5102 | Nokia | nokia | 76 | 83 5103 | Samsung | samsung | 84 | 87 5104 | Micromax | micromax | 88 | 89 5105 | Nokia Sub 1 | nokiasub1 | 77 | 78 5106 | Nokia Sub 2 | nokiasub2 | 79 | 82 5107 | Nokia Sub 3 | nokiasub3 | 80 | 81 5109 | Galaxy | galaxy | 85 | 86
i am retrieving Tree hierarchy of a particular node using this query:
$sql = "SELECT n.cat_ID,n.cat_name,n.cat_nicename, (COUNT(*)-1) AS depth FROM " .TBL_CATEGORIES. " AS n, " .TBL_CATEGORIES. " AS p WHERE p.cat_nicename='mobile' AND n.lft BETWEEN p.lft AND p.rgt GROUP BY n.lft ORDER BY n.lft;";
Msg#: 4505378 posted 10:26 am on Oct 8, 2012 (gmt 0)
I'm not sure I fully got how your tree is modeled in your database (all I'd expect is a parent id).
Maybe the first part is to explain just what lft and rgt mean in your table.
Anyway what I would do: - just keep the parent id for every category - load a tree in memory that can be walked efficiently (catid -> parentcatid) is all you need ... - And then you can walk up to the root from any catid, in the inmemory array, which gives you a list of cats to get the details like url, what to show etc from, you even get the order (to show them in - well reverse, but that's easy enough). [Although, no doubt somebody with more SQL skills than myself could walk the tree in SQL just as well]
Msg#: 4505378 posted 10:38 am on Oct 8, 2012 (gmt 0)
@swa66 .. thank you for the reply.. This tree is based on a nested set model "Nested set model" will give you first result of wiki. Please take a look and then may be you will be able to help me better :)
Msg#: 4505378 posted 12:17 pm on Oct 8, 2012 (gmt 0)
As I said: I only use a parentid for this. There are drawbacks to it too (esp. if you do not have a consistent or predictable depth), but it's at least a lot easier to repair should it ever go out of sync.
Msg#: 4505378 posted 12:23 pm on Oct 8, 2012 (gmt 0)
@swa66 .. Ahh i get it now.. :)
actually nested model set it very fast when you try to receive and display tree, only updation is a bit slow.. when you have lots of entries... single query does all the job in nested model, while ParentID one takes lots of quaries. So far all my work is done using single query only :)