Is it possible to add to different aliases to the same table in a dynamic query?

Question

I would like to use a example query for retrieving a full tree of the nested set model in a dynamic query.

SELECT node.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;

The problem is that dynamic queries can't alias the same table. Is there a work around for this?

Answer 1

The best answer I have at this point is to simply execute the subquery first using fetchAssoc() to return an array.

$subquery = db_select('nested_category', 'parent');
$subquery->addField('parent', 'lft');
$subquery->addField('parent', 'rgt');
$subquery->condition('name', $name, '=');
$parent = $subquery->execute()->fetchAssoc();

$query = db_select('nested_category', 'node');
$query->addField('node', 'name');
$query->condition('node.lft',$parent, 'BETWEEN');
$results = $query->execute()->fetchAll();