Re: How to self join?
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 the Google Groups
CakePHP group.
To unsubscribe from this group and stop receiving emails from it, send an email
to cake-php+unsubscr...@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.
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 i...@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+unsubscr...@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+unsubscr...@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.
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+unsubscr...@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.
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+unsubscr...@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.
