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 [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.
