Re: How to self join?

Sorry, Sam.

I realised that was a bum steer just before leaving from work, as I tried to do a similar thing and it failed.  That's because when the find is issued to do the belong association, it's a separate query, and the only thing that gets passed to it from the main model is the value of the foreignKey, but that must match the primaryKey of the belongsModel.

That virtualField setup wont quite work, because you can only do virtual fields on the current model.  However, if you do it on the current model, then when you try to query the parent, it should be available.

However, I think you're going to have to do use the joins clause. Here's my take on what the function on the model might look like.

public function getNodeCounts() {

    return $this->find('all', array(

   'fields' => array('CmsPage.name', '(count(CmsPageParent.name) - 1) as depth',

'joins => array(

array(

'table' => 'cms_pages',

'alias' => 'CmsPageParent',

'type' => 'left'

)

)

   'conditions' => array(

'CmsPage.lft between CmsPageParent.lft and CmsPageParent.rgt'

),

'group' => 'CmsPage.name',

'order' => 'CmsPage.lft'

));

}

There are a couple of notes. I'm not sure if the 'as depth' will work. You may find the depth value appears on an anonymous model, but perhaps with that field name.

You may need to stick array() around the group and order clauses.

I'm concerned that the joins entry doesn't have any conditions.  I'm not sure if this is how you'd normally do a full table join via the from clause using joins.

Failing that, just do a $db->getDataSource()->fetchAll().  Your query doesn't take any input, so there should be minimal issue with sql injection. http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#prepared-statements

Regards

Reuben Helms

On Tue, Jun 17, 2014 at 1:03 AM, Sam Clauw <info@bellewaerdefun.be> wrote:

Well, it seems there are 2 problems with you suggested query. Here's the SQL error I've got:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CmsPage.null' in 'on clause'
SQL Query:  SELECT `CmsPage`.`id`, `CmsPage`.`parent_id`, `CmsPage`.`name`, `CmsPage`.`lft`, `CmsPage`.`rgt`, `CmsPage`.`plugin`, `CmsPage`.`controller`, `CmsPage`.`action`, `CmsPage`.`show`, `CmsPage`.`sequence`, `CmsPage`.`created`, `CmsPage`.`modified`, `CmsPage`.`deleted`, `Parent`.`id`, `Parent`.`parent_id`, `Parent`.`name`, `Parent`.`lft`, `Parent`.`rgt`, `Parent`.`plugin`, `Parent`.`controller`, `Parent`.`action`, `Parent`.`show`, `Parent`.`sequence`, `Parent`.`created`, `Parent`.`modified`, `Parent`.`deleted` FROM `blwfun`.`cms_pages` AS `CmsPage` LEFT JOIN `blwfun`.`cms_pages` AS `Parent` ON (`CmsPage`.`null` = `Parent`.`id` AND `CmsPage`.`lft` BETWEEN `Parent`.`lft` and `Parent`.`rgt`) WHERE `CmsPage`.`deleted` IS NULL LIMIT 20

So, I changed my model to:

class CmsPage extends CoasterCmsAppModel

{

    public $belongsTo = array(

        'Parent' => array(

            'className' => 'CoasterCms.CmsPage',

            /*'foreignKey' => 'null',*/

            'conditions' => array(

                'CmsPage.lft BETWEEN Parent.lft and Parent.rgt'

            )

        )

    );

    

    public $virtualFields = array(

        'depth' => '(COUNT(Parent.name) - 1)'

    );

}

The only problem is now that I only got one row in return, with a wrong depth value...

Somebody who knows what I'm still doing wrong? Or somebody who know how I can output all SQL statements as a string? The only code I can find is:

$log = $this->Model->getDataSource()->getLog(false, false); debug($log);

But I cannot do anything with that output:

  array(  	'log' => array(),  	'count' => (int) 0,  	'time' => null  )

--
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 a topic in the Google Groups "CakePHP" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/cake-php/snf6LkO638M/unsubscribe.
To unsubscribe from this group and all its topics, 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.

--
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.