[Deprecated] DMZ 1.5.3 (DataMapper OverZealous Edition)

[eluser]Mirage[/eluser]
I'm finishing up a Tree extension implementing Modified Preorder Tree Traversal for DMZ. So far so good. Only a few methods to go, but I've run into a wall.

Basically, I wanted to avoid having the user define a self-relationship on the model and handle that in the extension. Is that possible?

Here is the original recipe from the mysql site:

Code:

// Find the immediate subordinates of a node
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
HAVING depth <= 1
ORDER BY node.lft;

So basically that requires a join. Can this be written [in the extension] without having to define a self relationship on the model?

I actually was able to do something like this by adding addressing selects and sources right to ActiveRecord. But it feels hacky, wrong and expectedly fails, because DataMapper adds the tablename to the FROM statement:

Code:

function get_children($node) {
        // get children by gettin descendants with count and limit that to 1
        // De-reference some vars for readability
        $lcol = $this->options['left_colname'];
        $rcol = $this->options['right_colname'];
        
        // Isolate the dependants of the parent which are 1 level away
        $node->db->select('node.*, (COUNT(parent.id)-1) as depth');
        $node->db->from("{$node->table} as node, {$node->table} as parent");
        
        // Only descendants, parent node not included
        $node->db->where("node.{$lcol} > parent.{$lcol}");
        $node->db->where("node.{$lcol} < parent.{$rcol}");
      
        // Only for the parents part of the tree  
        $node->db->where("node.{$lcol} >", $node->$lcol );
        $node->db->where("node.{$rcol} <", $node->$rcol );
                
        $node->db->group_by("node.id");
        $node->db->having("depth =",1);
        $node->db->order_by("node.{$lcol}");
        
        // Run query
        $node->get();
   }

The above generates this query for me:

Code:

SELECT `node`.*, (COUNT(parent.id)-1) as depth FROM (`apn_pages` as node, `apn_pages` as parent, `apn_pages`) WHERE `node`.`lft` > parent.lft AND `node`.`lft` < parent.rgt AND `node`.`lft` > '8' AND `node`.`rgt` < '15' GROUP BY `node`.`id` HAVING `depth` = 1 ORDER BY `node`.`lft`

As you can see there an extra `apn_pages` in the table list and thus the query won't return anything. If I edit the SQL to run this maunally I do get the expected results:

Code:

SELECT `node`.*, (COUNT(parent.id)-1) as depth FROM (`apn_pages` as node, `apn_pages` as parent) WHERE `node`.`lft` > parent.lft AND `node`.`lft` < parent.rgt AND `node`.`lft` > '8' AND `node`.`rgt` < '15' GROUP BY `node`.`id` HAVING `depth` = 1 ORDER BY `node`.`lft`

Suggestions?