Re: How to self join?

Hi Sam

You should definitely extend or implement it in the Model, so that it's available anywhere that the model is.

I've never actually used the BETWEEN keyword before, so I'll take your work for it that it works.

I might suggest setting up associations to assist.

You can make an existing Model have a relationship with itself.  For example, you might have:

public $belongsTo = array(

   'Parent' => array(

      'className' => 'CmsPage'

      'foreignKey' => null,

      'conditions' => array(

         'CmsPage.lft between Parent.lft and Parent.rgt'

      )

   );

I've made some assumptions with this.. there's no foreign key between the node and the parent node, other than the lft/rgt arrangement, thus the nulling of the foreignKey and provision of a condition for the relationship.

This is untested, and I'm not even sure it will work, but it is a start.

Actually, this section has a little bit on self joins [http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#multiple-relations-to-the-same-model], but it's not quite as complex as what you are after.

Failing that, I'd be looking at the 'joins' clause, which can do an inner or outer join as required, rather than a full table join in the from clause. [http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#joining-tables]

Regards

Reuben Helms

On Monday, 16 June 2014 04:08:08 UTC+10, Sam Clauw wrote:

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.