CakePPH 4 - Unable to limit results by condition in belongsToMany association

Need Help
1

Hi there!

I have this belongsToMany association: articles has many tags (through articles_tags). Here’s a visual that represents my connections:

CoasterCms
CoasterCms820×1501 135 KB

In a page called ‘tag x’, I want to print some tag related content (name, content, image, …) and I want to paginate all articles that has the ‘tag x’ linked to it. Unfortunately, I can’t figure out how to limit these article results. All articles are shown instead of the ones who has ‘tag x’ linked to.

Here’s my code. To start with, these are my models.

ArticlesTable (ArticlesTable.php)

<?php
declare(strict_types=1);

namespace Blog\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class ArticlesTable extends Table
{
    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('articles');
        $this->setPrimaryKey('id');
        $this->setDisplayField('name');

        // Behaviors

        $this->addBehavior(
            'CounterCache', [
                'Categories' => [
                    'no_articles',
                    'no_articles_visible' => [
                        'conditions' => [
                            'Articles.is_visible' => 1
                        ]
                    ]
                ]
            ]
        );
        
        $this->addBehavior('ImageProcess');

        $this->addBehavior('Timestamp');

        // Associations (belongsTo)

        $this->belongsTo('Blog.Categories')
            ->setForeignKey('category_id')
            ->setJoinType('INNER');

        $this->belongsTo('Languages')
            ->setForeignKey('language_id')
            ->setJoinType('INNER');

        $this->belongsTo('Users')
            ->setForeignKey('user_id')
            ->setJoinType('INNER');

        // Associations (hasMany)

        $this->hasMany(
            'Blog.ArticleComments', [
                'sort' => [
                    'ArticleComments.created' => 'desc'
                ]
            ])
            ->setForeignKey('article_id');

        $this->hasMany(
            'Blog.ArticleImages', [
                'sort' => [
                    'ArticleImages.lft' => 'asc',
                    'ArticleImages.id' => 'asc'
                ]
            ])
            ->setForeignKey('article_id');

        $this->hasMany(
            'Blog.ArticleTags', [
                'sort' => [
                    'Tags.name' => 'asc'
                ]
            ]        )
            ->setForeignKey('article_id');

        $this->hasMany('Links')
            ->setForeignKey('article_id');
    }
}

ArticleTagsTable (ArticleTagsTable.php)

<?php
declare(strict_types=1);

namespace Blog\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class ArticleTagsTable extends Table
{
    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('article_tags');
        $this->setPrimaryKey('id');
        $this->setDisplayField('id');

        // Behaviors

        $this->addBehavior(
            'CounterCache', [
                'Articles' => [
                    'no_tags'
                ]
            ]
        );
        
        $this->addBehavior('Timestamp');

        // Associations (belongsTo)

        $this->belongsTo('Blog.Articles')
            ->setForeignKey('article_id')
            ->setJoinType('INNER');
        
        $this->belongsTo('Blog.Tags')
            ->setForeignKey('tag_id')
            ->setJoinType('INNER');

        $this->belongsTo('Languages')
            ->setForeignKey('language_id')
            ->setJoinType('INNER');

        $this->belongsTo('Users')
            ->setForeignKey('user_id')
            ->setJoinType('INNER');
    }
}

TagsTable (TagsTable.php)

<?php
declare(strict_types=1);

namespace Blog\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class TagsTable extends Table
{
    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('tags');
        $this->setPrimaryKey('id');
        $this->setDisplayField('name');

        // Behaviors

        $this->addBehavior('ImageProcess');
        
        $this->addBehavior('Timestamp');

        // Associations (belongsTo)

        $this->belongsTo('Languages')
            ->setForeignKey('language_id')
            ->setJoinType('INNER');

        $this->belongsTo('Users')
            ->setForeignKey('user_id')
            ->setJoinType('INNER');

        // Associations (hasMany)

        $this->hasMany(
            'Blog.ArticleTags', [
                'sort' => [
                    'Articles.published' => 'desc'
                ]
            ])
            ->setForeignKey('tag_id');

        $this->hasMany('Links')
            ->setForeignKey('tag_id');
    }
}

Here’s my TagsController.php where I try to paginate the articles which are related to the specific tag:

<?php
declare(strict_types=1);

namespace Blog\Controller;

use Blog\Controller\AppController;
use Cake\I18n\FrozenTime;
use Cake\ORM\Query;

/**
 * Tags Controller
 *
 * @property \Blog\Model\Table\TagsTable $Tags
 * @method \Blog\Model\Entity\Tag[]|\Cake\Datasource\ResultSetInterface paginate($object = null, array $settings = [])
 */
class TagsController extends AppController
{
    public function initialize(): void
    {
        parent::initialize();

        $this->Authentication->allowUnauthenticated(['detail']); // Let guests see these actions.
    }
    
    public function detail($slug = null)
    {
        // Load related tables
        
        $this->Articles = $this->getTableLocator()->get('Blog.Articles');
        $this->Categories = $this->getTableLocator()->get('Blog.Categories');

        $tag = $this->Tags
            ->find()
            ->select([
                'Tags.id',
                'Tags.name',
                'Tags.content',
                'Tags.image',
                'Tags.meta_description',
                'Tags.meta_robots',
                'Tags.og_title',
                'Tags.og_description',
                'Tags.og_image'
            ])
            ->where([
                'Tags.slug' => $slug,
                'Tags.is_visible' => 1,
                'Tags.deleted IS' => null
            ])
            ->first();
        
        if (!$tag) {
            echo 'Tag doesn't exist.'; //404
            die();
        }

        $this->paginate = [
            'maxLimit' => 10
        ];

        $tagId = $tag->id;

        $articles = $this->paginate($this->Articles
            ->find()
            ->select([
                'Articles.id',
                'Articles.name',
                'Articles.content',
                'Articles.no_images_visible',
                'Articles.no_comments_visible',
                'Articles.no_tags',
                'Articles.slug',
                'Articles.published'
            ])
            ->contain([
                'ArticleImages' => function (Query $q) {
                    $q
                        ->select([
                            'ArticleImages.article_id',
                            'ArticleImages.id',
                            'ArticleImages.name',
                            'ArticleImages.alt',
                            'ArticleImages.title',
                            'ArticleImages.caption',
                            'ArticleImages.copyright'
                        ])
                        ->where([
                            'ArticleImages.is_visible' => 1,
                            'ArticleImages.deleted IS' => null
                        ]);

                    return $q;
                },
                'Categories' => function (Query $q) {
                    $q
                        ->select([
                            'Categories.id',
                            'Categories.name',
                            'Categories.slug'
                        ])
                        ->where([
                            'Categories.is_visible' => 1,
                            'Categories.deleted IS' => null
                        ]);
                    
                    return $q;
                },
                'ArticleTags.Tags' => function (Query $q) use ($tagId) {
                    $q
                        ->select([
                            'Tags.id',
                            'Tags.name',
                            'Tags.slug'
                        ])
                        ->where([
                            'Tags.id' => $tagId,
                            'Tags.is_visible' => 1,
                            'Tags.deleted IS' => null
                        ]);

                    return $q;
                }
            ])
            ->where([
                'Articles.is_visible' => 1,
                'Articles.published <' => FrozenTime::now(),
                'Articles.deleted IS' => null
            ])
            ->order([
                'Articles.published' => 'desc',
                'Articles.id' => 'desc'
            ])
            ->limit(10)
        );

        foreach ($articles as $article) {
            // Add article link
            $articleLink = $this->Articles->getLink($article->id, $this->request->getParam('language'));
            $article->link = $articleLink;

            // Add article comments link
            $articleCommentsLink = $this->Articles->getLink($article->id, $this->request->getParam('language'), __('comments'));
            $article->commentsLink = $articleCommentsLink;

            // Add category link
            $categoryLink = $this->Categories->getLink($article->category->id, $this->request->getParam('language'));
            $article->category->link = $categoryLink;

            // Add tag links
            foreach ($article->article_tags as $key => $articleTag) {
                $tagLink = $this->Tags->getLink($articleTag->tag->id, $this->request->getParam('language'));
                $article->article_tags[$key]->tag->link = $tagLink;
            }
        }

        $this->set(compact('tag', 'articles'));
    }
}

Does anybody have a clue what I could do to retrieve the desired result and to only select the articles with ‘tag x’ connected?

FYI: before I implemented the pagination, I succeeded to select only the articles with the ‘tag x’ linked. This was how my query in TagsController.php looked before:

class TagsController extends AppController
{
    public function initialize(): void
    {
        parent::initialize();

        $this->Authentication->allowUnauthenticated(['detail']); // Let guests see these actions.
    }
    
    public function detail($slug = null)
    {
        ...
        $query = $this->Tags
            ->find()
            ->select([
                'Tags.id',
                'Tags.name',
                'Tags.content',
                'Tags.image',
                'Tags.meta_description',
                'Tags.meta_robots',
                'Tags.og_title',
                'Tags.og_description',
                'Tags.og_image'
            ])
            ->contain([
                'ArticleTags.Articles' => function (Query $q) {
                    return $q
                        ->select([
                            'Articles.id',
                            'Articles.category_id',
                            'Articles.name',
                            'Articles.content',
                            'Articles.no_images_visible',
                            'Articles.no_comments_visible',
                            'Articles.no_tags',
                            'Articles.slug',
                            'Articles.published'
                        ])
                        ->where([
                            'Articles.is_visible' => 1,
                            'Articles.published <' => FrozenTime::now(),
                            'Articles.deleted IS' => null
                        ]);
                },
                'ArticleTags.Articles.ArticleImages' => function (Query $q) {
                    return $q
                        ->select([
                            'ArticleImages.article_id',
                            'ArticleImages.id',
                            'ArticleImages.name',
                            'ArticleImages.alt',
                            'ArticleImages.title'
                        ])
                        ->where([
                            'ArticleImages.is_visible' => 1,
                            'ArticleImages.deleted IS' => null
                        ]);
                },
                'ArticleTags.Articles.Categories' => function (Query $q) {
                    return $q
                        ->select([
                            'Categories.id',
                            'Categories.name',
                            'Categories.slug'
                        ])
                        ->where([
                            'Categories.is_visible' => 1,
                            'Categories.deleted IS' => null
                        ]);
                },
                'ArticleTags.Articles.ArticleTags.Tags' => function (Query $q) {
                    return $q
                        ->select([
                            'Tags.id',
                            'Tags.name',
                            'Tags.slug'
                        ])
                        ->where([
                            'Tags.is_visible' => 1,
                            'Tags.deleted IS' => null
                        ]);
                }
            ])
            ->where([
                'Tags.slug' => $slug,
                'Tags.is_visible' => 1,
                'Tags.deleted IS' => null
            ])
            ->first();
        
        ...
    }
}

So I don’t know why this doens’t work with pagination :slight_smile:

Thanks a lot for reading and for helping!

Kind regards,
Sam

2

Sounds like you may want matching?

2 Likes
3

I’d also recommend you watch my video about fetching associated data which explains the difference between contain and matching :wink:

2 Likes
4

This is exactly what I needed @Zuluru! Very happy with this solution, I was wondering multiple hours why it wasn’t working. I do use the CakePHP 4 manual often, but I think I didn’t read the ‘mathing’-part very carefully. Thanks a lot!

5

@Kevin: wow, thanks for your efforts for making all those videos. I didn’t knew the existince of it, so I quickly discribed to the channel :wink:
And indeed: ‘matching’ and ‘distinct’ was exactly what I needed. I post my updated ‘TagsController.php’-code here for other people who had troubles with this topic:

<?php
declare(strict_types=1);

namespace Blog\Controller;

use Blog\Controller\AppController;
use Cake\I18n\FrozenTime;
use Cake\ORM\Query;

/**
 * Tags Controller
 *
 * @property \Blog\Model\Table\TagsTable $Tags
 * @method \Blog\Model\Entity\Tag[]|\Cake\Datasource\ResultSetInterface paginate($object = null, array $settings = [])
 */
class TagsController extends AppController
{
    public function initialize(): void
    {
        parent::initialize();

        $this->Authentication->allowUnauthenticated(['detail']); // Let guests see these actions.
    }
    
    public function detail($slug = null)
    {
        // Load related tables
        
        $this->Articles = $this->getTableLocator()->get('Blog.Articles');
        $this->Categories = $this->getTableLocator()->get('Blog.Categories');

        $tag = $this->Tags
            ->find()
            ->select([
                'Tags.id',
                'Tags.name',
                'Tags.content',
                'Tags.image',
                'Tags.meta_description',
                'Tags.meta_robots',
                'Tags.og_title',
                'Tags.og_description',
                'Tags.og_image'
            ])
            ->where([
                'Tags.slug' => $slug,
                'Tags.is_visible' => 1,
                'Tags.deleted IS' => null
            ])
            ->first();
        
        if (!$tag) {
            echo 'Tag bestaat niet.'; //404
            die();
        }

        $this->paginate = [
            'maxLimit' => 10
        ];

        $tagId = $tag->id; // $category->id cannot be inherited in the anonymous function

        $articles = $this->paginate($this->Articles
            ->find()
            ->select([
                'Articles.id',
                'Articles.name',
                'Articles.content',
                'Articles.no_images_visible',
                'Articles.no_comments_visible',
                'Articles.no_tags',
                'Articles.slug',
                'Articles.published'
            ])
            ->contain([
                'ArticleImages' => function (Query $q) {
                    $q
                        ->select([
                            'ArticleImages.article_id',
                            'ArticleImages.id',
                            'ArticleImages.name',
                            'ArticleImages.alt',
                            'ArticleImages.title',
                            'ArticleImages.caption',
                            'ArticleImages.copyright'
                        ])
                        ->where([
                            'ArticleImages.is_visible' => 1,
                            'ArticleImages.deleted IS' => null
                        ]);

                    return $q;
                },
                'Categories' => function (Query $q) {
                    $q
                        ->select([
                            'Categories.id',
                            'Categories.name',
                            'Categories.slug'
                        ])
                        ->where([
                            'Categories.is_visible' => 1,
                            'Categories.deleted IS' => null
                        ]);
                    
                    return $q;
                },
                'ArticleTags.Tags' => function (Query $q) {
                    $q
                        ->select([
                            'Tags.id',
                            'Tags.name',
                            'Tags.slug'
                        ])
                        ->where([
                            'Tags.is_visible' => 1,
                            'Tags.deleted IS' => null
                        ]);

                    return $q;
                }
            ])
            ->matching('ArticleTags.Tags', function ($q) use ($tagId) {
                $q
                    ->select([
                        'Tags.id',
                        'Tags.name',
                        'Tags.slug'
                    ])
                    ->where([
                        'Tags.id' => $tagId,
                        'Tags.is_visible' => 1,
                        'Tags.deleted IS' => null
                    ]);

                return $q;
            })
            ->distinct('Articles.id')
            ->where([
                'Articles.is_visible' => 1,
                'Articles.published <' => FrozenTime::now(),
                'Articles.deleted IS' => null
            ])
            ->order([
                'Articles.published' => 'desc',
                'Articles.id' => 'desc'
            ])
            ->limit(10)
        );

        foreach ($articles as $article) {
            // Add article link
            $articleLink = $this->Articles->getLink($article->id, $this->request->getParam('language'));
            $article->link = $articleLink;

            // Add article comments link
            $articleCommentsLink = $this->Articles->getLink($article->id, $this->request->getParam('language'), __('comments'));
            $article->commentsLink = $articleCommentsLink;

            // Add category link
            $categoryLink = $this->Categories->getLink($article->category->id, $this->request->getParam('language'));
            $article->category->link = $categoryLink;

            // Add tag links
            foreach ($article->article_tags as $key => $articleTag) {
                $tagLink = $this->Tags->getLink($articleTag->tag->id, $this->request->getParam('language'));
                $article->article_tags[$key]->tag->link = $tagLink;
            }
        }

        $this->set(compact('tag', 'articles'));
    }
}
1 Like