0

I have 3 models in my CakePHP application that are linked with a HABTM association following the CakePHP naming conventions: posts - post_tag_links - tags

In my front-end postController.php file, I want to get query all the posts with their related tags. With my query, I can get the information out of the Post en the PostTagLink model, but I don't now how I can reach the info of the Tag model so I can get the tag titles and the tag slugs. Here's my attempt:

$this->Post->virtualFields = array(
    'date_published' => 'DATE_FORMAT(Post.published, "%d/%m/%Y")',
    'time_published' => 'DATE_FORMAT(Post.published, "%ku%i")',
    'day' => 'DATE_FORMAT(Post.published, "%d")',
    'month' => 'DATE_FORMAT(Post.published, "%m")',
    'year' => 'DATE_FORMAT(Post.published, "%Y")',
    'hours' => 'DATE_FORMAT(Post.published, "%k")',
    'minutes' => 'DATE_FORMAT(Post.published, "%i")'
);

$this->Paginator->settings = array(
    'fields' => array(
        'Post.id',
        'Post.title',
        'Post.content',
        'Post.published',
        'Post.slug',
        'Post.date_published',
        'Post.time_published',
        'Post.day',
        'Post.month',
        'Post.year'
    ),
    'conditions' => array(
        'Post.published <' => date('Y-m-d H:i:s'),
        'Post.show' => 'Y',
        'Post.deleted' => null
    ),
    'order' => array(
        'Post.published' => 'DESC',
        'Post.id' => 'DESC',
    ),
    'limit' => 10
);

$posts = $this->Paginator->paginate(
    'Post'
);

The output from this code is close but not close enough:

array(
    (int) 0 => array(
        'Post' => array(
            'id' => '1',
            'title' => 'Eerste post',
            'content' => 'Content 1',
            'published' => '2016-03-16 18:56:00',
            'slug' => 'eerste-post',
            'date_published' => '16/03/2016',
            'time_published' => '18u56',
            'day' => '16',
            'month' => '03',
            'year' => '2016'
        ),
        'PostTagLink' => array(
            (int) 0 => array(
                'id' => '30',
                'post_id' => '1',
                'tag_id' => '1',
                'created' => '2016-03-05 14:05:48',
                'modified' => '2016-03-05 14:05:48',
                'deleted' => null
            ),
            (int) 1 => array(
                'id' => '31',
                'post_id' => '1',
                'tag_id' => '3',
                'created' => '2016-03-05 15:10:53',
                'modified' => '2016-03-05 15:10:53',
                'deleted' => null
            ),
            (int) 2 => array(
                'id' => '32',
                'post_id' => '1',
                'tag_id' => '2',
                'created' => '2016-03-05 15:10:53',
                'modified' => '2016-03-05 15:10:53',
                'deleted' => null
            ),
            (int) 3 => array(
                'id' => '36',
                'post_id' => '1',
                'tag_id' => '5',
                'created' => '2016-03-20 01:59:41',
                'modified' => '2016-03-20 01:59:41',
                'deleted' => null
            )
        )
    ),
    (int) 1 => array(
        'Post' => array(
            'id' => '2',
            'title' => 'Tweede post',
            'content' => 'Content 2',
            'published' => '2016-02-29 18:59:00',
            'slug' => 'tweede-post',
            'date_published' => '29/02/2016',
            'time_published' => '18u59',
            'day' => '29',
            'month' => '02',
            'year' => '2016'
        ),
        'PostTagLink' => array(
            (int) 0 => array(
                'id' => '37',
                'post_id' => '2',
                'tag_id' => '6',
                'created' => '2016-03-20 01:59:56',
                'modified' => '2016-03-20 01:59:56',
                'deleted' => null
            )
        )
    ),
    (int) 2 => array(
        'Post' => array(
            'id' => '3',
            'title' => 'Derde post',
            'content' => 'Content 3',
            'published' => '2016-01-22 19:00:00',
            'slug' => 'derde-post',
            'date_published' => '22/01/2016',
            'time_published' => '19u00',
            'day' => '22',
            'month' => '01',
            'year' => '2016'
        ),
        'PostTagLink' => array(
            (int) 0 => array(
                'id' => '26',
                'post_id' => '3',
                'tag_id' => '4',
                'created' => '2016-01-23 14:12:52',
                'modified' => '2016-01-23 14:12:52',
                'deleted' => null
            ),
            (int) 1 => array(
                'id' => '34',
                'post_id' => '3',
                'tag_id' => '1',
                'created' => '2016-03-09 22:24:33',
                'modified' => '2016-03-09 22:24:33',
                'deleted' => null
            )
        )
    ),
    (int) 3 => array(
        'Post' => array(
            'id' => '4',
            'title' => 'Vierde post',
            'content' => 'Content 4',
            'published' => '2016-01-11 19:00:00',
            'slug' => 'vierde-post',
            'date_published' => '11/01/2016',
            'time_published' => '19u00',
            'day' => '11',
            'month' => '01',
            'year' => '2016'
        ),
        'PostTagLink' => array(
            (int) 0 => array(
                'id' => '33',
                'post_id' => '4',
                'tag_id' => '1',
                'created' => '2016-03-09 22:24:25',
                'modified' => '2016-03-09 22:24:25',
                'deleted' => null
            )
        )
    ),
    (int) 4 => array(
        'Post' => array(
            'id' => '5',
            'title' => 'Vijfde post',
            'content' => 'Content 5',
            'published' => '2015-05-23 09:54:00',
            'slug' => 'vijfde-post',
            'date_published' => '23/05/2015',
            'time_published' => '9u54',
            'day' => '23',
            'month' => '05',
            'year' => '2015'
        ),
        'PostTagLink' => array(
            (int) 0 => array(
                'id' => '25',
                'post_id' => '5',
                'tag_id' => '2',
                'created' => '2016-01-23 14:11:22',
                'modified' => '2016-01-23 14:11:22',
                'deleted' => null
            ),
            (int) 1 => array(
                'id' => '27',
                'post_id' => '5',
                'tag_id' => '4',
                'created' => '2016-01-23 14:14:11',
                'modified' => '2016-01-23 14:14:11',
                'deleted' => null
            ),
            (int) 2 => array(
                'id' => '29',
                'post_id' => '5',
                'tag_id' => '1',
                'created' => '2016-02-27 17:02:02',
                'modified' => '2016-02-27 17:02:02',
                'deleted' => null
            ),
            (int) 3 => array(
                'id' => '38',
                'post_id' => '5',
                'tag_id' => '5',
                'created' => '2016-03-20 02:02:14',
                'modified' => '2016-03-20 02:02:14',
                'deleted' => null
            ),
            (int) 4 => array(
                'id' => '39',
                'post_id' => '5',
                'tag_id' => '7',
                'created' => '2016-03-20 02:20:34',
                'modified' => '2016-03-20 02:20:34',
                'deleted' => null
            )
        )
    )
)

As you can see, there's no additional Tag information available: I only got the link table information. One of my attempts was to add the joins to the query:

$this->Post->virtualFields = array(
    'date_published' => 'DATE_FORMAT(Post.published, "%d/%m/%Y")',
    'time_published' => 'DATE_FORMAT(Post.published, "%ku%i")',
    'day' => 'DATE_FORMAT(Post.published, "%d")',
    'month' => 'DATE_FORMAT(Post.published, "%m")',
    'year' => 'DATE_FORMAT(Post.published, "%Y")',
    'hours' => 'DATE_FORMAT(Post.published, "%k")',
    'minutes' => 'DATE_FORMAT(Post.published, "%i")'
);

$this->Paginator->settings = array(
    'fields' => array(
        'Post.id',
        'Post.title',
        'Post.content',
        'Post.published',
        'Post.slug',
        'Post.date_published',
        'Post.time_published',
        'Post.day',
        'Post.month',
        'Post.year'
    ),
    'joins' => array(
        array(
            'table' => 'post_tag_links',
            'alias' => 'PostTagLink',
            'type' => 'inner',
            'conditions' => 'Post.id = PostTagLink.post_id'
        ),
        array(
            'table' => 'tags',
            'alias' => 'Tag',
            'type' => 'inner',
            'conditions' => 'Tag.id = PostTagLink.tag_id'
        )
    ),
    'conditions' => array(
        'Post.published <' => date('Y-m-d H:i:s'),
        'Post.show' => 'Y',
        'Post.deleted' => null,
        $tagCondition
    ),
    'order' => array(
        'Post.published' => 'DESC',
        'Post.id' => 'DESC',
    ),
    'limit' => 999
);

$posts = $this->Paginator->paginate(
    'Post'
);

Here's the problem that I have multiple times the same Post in the query results withouh returning the Tag information of course :P.

Anyone who can help me on this one? ;)

Edit

Here are my model declarations.

Post.php

App::uses('AppModel', 'Model');
class Post extends AppModel
{
    public $hasMany = array(
        'PostComment' => array(
            'className' => 'PostComment',
            'foreignKey' => 'post_id',
            'dependent' => true
        ),
        'PostPhoto' => array(
            'className' => 'PostPhoto',
            'foreignKey' => 'post_id',
            'order' => 'PostPhoto.sequence ASC',
            'conditions' => array(
                'PostPhoto.show' => 'Y'
            ),
            'dependent' => true
        ),
        'PostTagLink' => array(
            'className' => 'PostTagLink',
            'foreignKey' => 'post_id'
        )
    );
}

PostTagLink.php

App::uses('AppModel', 'Model');
class PostTagLink extends AppModel
{
    public $belongsTo = array(
        'Post' => array(
            'className' => 'Post',
            'foreignKey' => 'post_id'
        ),
        'Tag' => array(
            'className' => 'Tag',


    'foreignKey' => 'tag_id'
    )
);

}

Tag.php

App::uses('AppModel', 'Model');
class Tag extends AppModel
{
    public $hasMany = array(
        'PostTagLink' => array(
            'className' => 'PostTagLink',
            'foreignKey' => 'tag_id'
        )
    );
}
Sam
  • 472
  • 1
  • 8
  • 19
  • Show your models. It would be good to know how did you defined relations. And how is model recursive param set? – Bart Mar 20 '16 at 23:34
  • Thanks for your reply. I've updated my original post and I hope you have enough information with it? I didn't use recursive param set I guess ;) – Sam Mar 22 '16 at 21:24
  • 1
    Hmm, looks ok. Try to set `recursive` param in settings array `'recursive'=>1` or `'recursive'=>2`. And use version with no joins. You can also try `hasAndBelongsToMany` relations in your models – Bart Mar 23 '16 at 12:56
  • Fantastic, that works great for me! Thank you very much Bart, i'ts a big step in the good direction ;) – Sam Mar 24 '16 at 18:46

0 Answers0