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 <[email protected]> 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
> [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
