How to self join?

Hi there,

what I try to do is to make a self join in my controller. The only problem is I can't find any documentation about it in the cookbook 2.x about it.

My "uncaked" query looks like this:

SELECT

            node.name, (COUNT(parent.name) - 1) AS depth

FROM

            cms_pages AS node,

            cms_pages AS parent

WHERE

            node.lft BETWEEN parent.lft AND parent.rgt

GROUP BY

            node.name

ORDER BY

            node.lft

This is my current code that should be extended:

$mainMenu = $this->CmsPage->find('all', array(

    'conditions' => array(

        'CmsPage.lft BETWEEN ? AND ?' => array(

            $mainMenuRoot['CmsPage']['lft'],

            $mainMenuRoot['CmsPage']['rgt']

        ),

        'CmsPage.deleted' => null

    ),

    'order' => array(

        'CmsPage.lft ASC'

    )

));

Is there someone who has a solution for this? Can I solve it in my controller, of should I extend my model? :)

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.