Help! Complex tree model pagination and paginateCount with wrong results

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 cake-php@googlegroups.com.
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.