DataMapper ORM v1.8.0

[eluser]rherriman[/eluser]
I'm running into issues with subqueries and method chaining. I'm using DM from TIP, which I needed to resolve issue #32, which seemingly affected subqueries in general. I'm also using the nested sets extension for a site's category hierarchy. Here's some sample code.

Code:

class Category extends DataMapper {
    public $default_order_by = array('left_id');
    public $extensions = array('nestedsets');

    public function or_where_depth($level) {
        return $this->or_where_subquery(intval($level), $this->_depth_subquery());
    }

    public function select_depth($alias = 'depth') {
        return $this->select_subquery($this->_depth_subquery(), $alias);
    }

    public function where_depth($level) {
        return $this->where_subquery(intval($level), $this->_depth_subquery());
    }

    /**
     * Builds and returns a subquery that calculates node depth.
     */
    protected function _depth_subquery() {
        return $this->get_clone()
            ->select_func('COUNT', '@id', 'depth')
            ->where('${parent}.left_id > left_id AND ${parent}.left_id < right_id');
    }
}

Now the scenario: I want to write a single query that gives me every category at node depth 1 OR 2, ordered by left_id, with the depth of the node being included in the SELECT statement. This way, I can loop through the output in order and build a nested unordered list. If the node has a depth of 1, it is a parent in the context of this list, and the following nodes of depth 2 are its children, until it hits another node with a depth of 1. The DM query I tried building looks like this:

Code:

$category_list = new Category();
$category_list->select_depth()->where_depth(1)->or_where_depth(2)->order_by('left_id')->get();

Here is the query it generates:

Code:

SELECT (SELECT COUNT(`categories_subquery`.`id`) AS depth
FROM `categories` `categories_subquery`
WHERE `categories`.`left_id` > left_id AND categories.left_id < right_id) AS depth
FROM (`categories`)
WHERE 1 =(SELECT (SELECT COUNT(`categories_subquery`.`id`) AS depth
     FROM `categories` `categories_subquery` `categories_subquery`
     WHERE `categories_subquery`.`left_id` > left_id AND `categories_subquery`.left_id < right_id) AS depth, COUNT(`categories_subquery`.`id`) AS depth
    FROM `categories` `categories_subquery`
    WHERE `categories`.`left_id` > left_id AND categories.left_id < right_id)
OR 2 =(SELECT (SELECT COUNT(`categories_subquery`.`id`) AS depth
     FROM `categories` `categories_subquery` `categories_subquery`
     WHERE `categories_subquery`.`left_id` > left_id AND `categories_subquery`.left_id < right_id) AS depth, COUNT(`categories_subquery`.`id`) AS depth
    FROM `categories` `categories_subquery`
    WHERE 1 =(SELECT (SELECT COUNT(`categories_subquery`.`id`) AS depth
         FROM `categories` `categories_subquery` `categories_subquery` `categories_subquery`
         WHERE `categories_subquery`.`left_id` > left_id AND `categories_subquery`.left_id < right_id) AS depth, COUNT(`categories_subquery`.`id`) AS depth
        FROM `categories` `categories_subquery` `categories_subquery`
        WHERE `categories_subquery`.`left_id` > left_id AND `categories_subquery`.left_id < right_id)
    AND `categories`.`left_id` > left_id AND categories.left_id < right_id)
ORDER BY `categories`.`left_id`

This is quite hard to parse, but hopefully it is apparent what is going on. The chaining is causing all previously called subqueries to be included in subsequent subqueries. Note the table name aliasing:

Code:

...FROM `categories` `categories_subquery` `categories_subquery` `categories_subquery`...

Is this a bug in DataMapper itself, or am I simply missing something? Suggested fixes?

Thank you.