cakephp paginate multiple habtm

Question

hi guys,

i have multiple habtm like these :

// User model
var $hasMany = array('Post');

// Post model
var $hasAndBelongsToMany = array('Category', 'Tag');

// Category model
var $hasAndBelongsToMany = array('Post');

// Tag model
var $hasAndBelongsToMany = array('Post');

I tried to fetch all post along with its user and tags (within a certain category), somehow if i fetch tags, the result was wrong.

$this->paginate = array
(
    'Post' => array
    (
        'limit' => 2,

        'fields' => array(
            'Post.title', 'Post.content', 'Post.slug', 'Post.created',
            'Tag.name',
            'User.username', 'User.created', 'User.post_count', 'User.avatar_file_name'),

        'joins' => array
        (
            array(
                'table' => 'categories_posts',
                'alias' => 'CategoriesPost',
                'type' => 'inner',
                'conditions'=> array('CategoriesPost.post_id = Post.id')
            ),

            // FETCH USER
            array(
                'table' => 'users',
                'alias' => 'User',
                'type' => 'inner',
                'conditions'=> array('Post.user_id = User.id')
            ),

            // FETCH TAGS
            array(
                'table' => 'posts_tags',
                'alias' => 'PostsTag',
                'type' => 'inner',
                'conditions'=> array('PostsTag.post_id = Post.id')
            ),

            array(
                'table' => 'tags',
                'alias' => 'Tag',
                'type' => 'inner',
                'conditions'=> array('Tag.id = PostsTag.tag_id')
            ),

            array(
                'table' => 'categories',
                'alias' => 'Category',
                'type' => 'inner',
                'conditions'=> array('Category.id = CategoriesPost.category_id', 'Category.slug' => $slug)
            )
        )
    )
);

$posts = $this->paginate();

could anyone gimme a solution since i'm a newbie? many thanks...

Answer 1

I've run into a similar problem, when trying to paginate a resultset using an associated model. I ended up having to manually bind my models together, run the query, and then unbind them in order to get Cake to contain the right data together. ( http://book.cakephp.org/view/86/Creating-and-Destroying-Associations-on-the-Fly )

You could also try the containable behaviour which will allow you to specify which models you want to include in your result set. Containable is core in 1.2+ ( http://book.cakephp.org/view/474/Containable ), otherwise you'll need to grab it yourself.

I'm not too sure on why you have such a gargantuan query there though. I would be more inclined to do something similar to the following.

$this->Model->recursive = 2;
$this->Model->paginate();

And let Cake get all the related data for me through my associations. Then I would adjust the return using a conditions array ( http://api.cakephp.org/class/controller#method-Controllerpaginate ) to specify the category.

Sorry it's not a defacto solution, but I'm a CakePHP amateur myself! You might find it easier to view the queries, results etc, using DebugKit, http://github.com/cakephp/debug_kit