Hi there,
I'm working on a complex site, where the model 'post' has a tree structure.
This structure allows me to organize the cascading content.
Table posts has a field 'type' by which a content type, 0 tells us that the
order is a group of articles, and 1 represents article.
I also have another table 'levels_posts' (self HABTM related), which allows
me from any deep-level of parent, to retrieve all child articles.
I have the following piece of code that works correctly:
$this -> Post -> recursive = -1;
$this -> paginate = array(
'joins' => array(
array(
'table' => 'levels_posts', // self HABTM or posts HABTM posts
'alias' => 'Level',
'type' => 'inner',
'conditions' => array(
'Level.level_id ' => $check_post['Post']['id'],
),
),
array(
'table' => 'posts',
'alias' => 'Related', // children post
'type' => 'inner',
'conditions' => array(
'Related.id = Level.post_id',
'Related.type' => 1, // where is post type 1 (article)
),
),
array(
'table' => 'posts',
'alias' => 'Parent', / return first parent level
'type' => 'inner',
'conditions' => array(
'Parent.id = Related.parent_id',
),
),
array(
'table' => 'pictures',
'alias' => 'Picture',
'type' => 'left',
'conditions' => array(
'Picture.post_id = Related.id',
),
),
),
'group' => 'Related.id',
'fields' => array('Related.*','Picture.*','Parent.title'),
'limit' => 20,
);
$this -> set('posts', $this -> paginate());
But the problem is that we paginateCount returns an incorrect result,
Instead of this query
SELECT COUNT(*) AS `count` FROM `farm_ba`.`posts` AS `Post` inner JOIN
`farm_ba`.`levels_posts` AS `Level` ON (`Level`.`level_id` = '1') inner
JOIN `farm_ba`.`posts` AS `Related` ON (`Related`.`id` = `Level`.`post_id`
AND `Related`.`type` = 1) inner JOIN `farm_ba`.`posts` AS `Parent` ON
(`Parent`.`id` = `Related`.`parent_id`) left JOIN `farm_ba`.`pictures` AS
`Picture` ON (`Picture`.`post_id` = `Related`.`id`) WHERE 1 = 1 GROUP BY
`Related`.`id`
I need a simple query like this
SELECT COUNT( * ) AS `count` FROM `farm_ba`.`levels_posts` AS `Level`
WHERE `Level`.`level_id` = 17"; // $check_post['Post']['id'];
This query gives me the correct number of children articles.
I need help to solve this, please point me where I am wrong. Thank you very
much.
Nikola
--
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 post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
