How to compare Query conditions from a BelongsToMany association in CakePHP 4.x?

Question

I have a working Posts, Categories and Tags relationship. (Posts BelongsTo Categories and BelongsToMany Tags.) They work just fine at my view and index actions with no issue.

Now, for a simple "search" functionality I'm working with the Query builder. I managed to make it successfully search Posts related to my query, as long as the terms are compared to fields from Posts and Categories, but I would also want to make it work with Tags.

This is my (working) Controller:

public function search()
{   
    $search = $this->request->getQuery('query');
    
    $posts = $this->Posts->find('all');
        
    $posts->contain(['Categories','Tags']);
    
    if(!empty($search)) {
        $posts->where(['or' => [
            ['Posts.title LIKE' => '%'.$search.'%', 'Posts.status' => 'published'],
            ['Posts.content LIKE' => '%'.$search.'%', 'Posts.status' => 'published'],
            ['Categories.name LIKE' => '%'.$search.'%','Posts.status' => 'published'],
        ]]);
    } else {
        $posts->where(['Posts.status' => 'published']);
    };
    
    $posts = $this->paginate($posts);

    $this->set(compact('posts'));
}

These are my (working) Models:

// Posts Table

class PostsTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);

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

        $this->belongsTo('Categories', [
            'foreignKey' => 'category_id',
            'joinType' => 'INNER',
        ]);
        
        $this->belongsToMany('Tags',[
            'foreignKey' => 'post_id',
            'targetForeignKey' => 'tag_id',
            'joinTable' => 'posts_tags'
        ]);
    }
}

// Categories Table

class CategoriesTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);

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

        $this->hasMany('Posts', [
            'foreignKey' => 'category_id',
        ]);
    }
}

// Tags Table

class TagsTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);

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

        $this->belongsToMany('Posts', [
            'foreignKey' => 'tag_id',
            'targetForeignKey' => 'post_id',
            'joinTable' => 'posts_tags',
        ]);
    }
}

// PostsTags Table

class PostsTagsTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);

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

        $this->belongsTo('Posts', [
            'foreignKey' => 'post_id',
            'joinType' => 'INNER',
        ]);
        $this->belongsTo('Tags', [
            'foreignKey' => 'tag_id',
            'joinType' => 'INNER',
        ]);
    }
}

And this is my view:

<?php $search = $this->request->getQuery('query'); ?>
<div class="posts index content">
    <h1>Search Posts</h1>
    <?= $this->Form->create(NULL,['type' => 'get']) ?>
        <?= $this->Form->control('query',['default' => $search]) ?>
        <?= $this->Form->button('submit') ?>
    <?= $this->Form->end() ?>

    <?php foreach ($posts as $post): ?>
        <div class="card">
        <!-- Here goes the Post data -->
        </div>
    <?php endforeach; ?>
</div>
<div class="paginator">
    <ul class="pagination">
        <?= $this->Paginator->first('<< ' . __('first')) ?>
        <?= $this->Paginator->prev('< ' . __('previous')) ?>
        <?= $this->Paginator->numbers() ?>
        <?= $this->Paginator->next(__('next') . ' >') ?>
        <?= $this->Paginator->last(__('last') . ' >>') ?>
    </ul>
    <p><?= $this->Paginator->counter(__('Page {{page}} of {{pages}}')) ?></p>
</div>

So when I submit the form, it filters my posts according to those conditions. But when I try adding a field from my Tags model to the search query, it breaks.

I tried adding the line:

['Tags.name LIKE' => '%'.$search.'%', 'Posts.status' => 'published']

...under:

['Categories.name LIKE' => '%'.$search.'%','Posts.status' => 'published']

But then when I introduce a query term, it throws me a "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Tags.name' in 'where clause'" error.

Same thing happens if instead of "$posts->where(...)" I use the "$posts->find('all',['conditions' => [...]]):" option.

So I'm stumped... How can I search a term within a HABTM relationship?

What am I missing?

Answer 1

User ndm's comment did the trick. So in case it wasn't clear enough (and if someone ever finds the same issue I did in the future), here's my final working controller:

public function search()
{   
    $search = $this->request->getQuery('query');

    $posts = $this->Posts->find('all')
        ->leftJoinWith('Tags')
        ->group(['Posts.id']);
    
    $posts->contain(['Categories','Tags']);

    if(!empty($search)) {
        $posts->where(['or' => [
            ['Posts.title LIKE' => '%'.$search.'%', 'Posts.status' => 'published'],
            ['Posts.content LIKE' => '%'.$search.'%', 'Posts.status' => 'published'],
            ['Categories.name LIKE' => '%'.$search.'%','Posts.status' => 'published'],
            ['Tags.name LIKE' => '%'.$search.'%','Posts.status' => 'published']
        ]]);
    } else {
        $posts->where(['Posts.status' => 'published']);
    };

    $posts = $this->paginate($posts);

    $this->set(compact('posts'));
}