Welcome Guest, Not a member yet? Register   Sign In
Nested Set - Depth of a Sub-Tree - Active Record
#1

[eluser]Otemu[/eluser]
Hi,

After reading the excellent managing hierarchical data in mysql I was wondering if anyone knows how to write the query below in active record or if it even possible with AR:

Code:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;

Since all my queries currently in AR be nice to continue that trend, however if not then I guess I have to run this one as standard mysql.

Any help be appreciated.
#2

[eluser]PhilTem[/eluser]
It's not possible with CI's standard AR-class since you got a subquery. There are however some techniques that allow subqueries, like you can find here or here (just to name a few).

I personally never looked into this subqueries thingy because I didn't need it so far. Or if I needed it most of the times I was able to just run two separate queries and combine the results according to my needs.
But since I'm working with Nested Sets at the moment what seems to be much more difficult than standard table layouts, I guess there's no way to avoid subqueries Tongue
#3

[eluser]Otemu[/eluser]
Thanks for your feedback and links that helps a lot Smile




Theme © iAndrew 2016 - Forum software by © MyBB