Re: Help! Complex tree model pagination and paginateCount with wrong results

Hi Nikola,

have you tried using 'contain' (Containable behavior) instead of the 'joins' you mention? 

Marek

Dne pondělí, 7. ledna 2013 14:36:59 UTC+1 Salines napsal(a):

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.