如何比较cakephp 4.x中属于属性协会的查询条件？

Question

我有一个有效的帖子，类别和标签关系。 （帖子属于属于类别和属于属于的标签。）它们在我的看法和索引动作中效果很好，没有问题。

现在，对于一个简单的“搜索”功能，我正在使用查询构建器。只要将术语与帖子和类别的字段进行比较，我设法成功地搜索了与我的查询有关的帖子，但是我也想让它与标签一起使用。

这是我的（工作）控制器：

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'));
}

这些是我的（工作）模型：

// 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',
        ]);
    }
}

这是我的观点：

<?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>

因此，当我提交表格时，它会根据这些条件过滤我的帖子。但是，当我尝试将一个从标签模型添加到搜索查询的字段时，它会破裂。

我尝试添加该行：

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

...下：

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

但是当我引入查询术语时，它给我扔了一个“ sqlstate [42S22]：找不到列：1054未知列'tags.name in in where子句'”错误。

如果我使用“ $ post-＆gt; where（...）”我使用“ $ posts-＆gt; find（'all'，['presenta'=＆gt; [...]]））：“也会发生同样的事情。选项。

所以我很困惑...如何在HABTM关系中搜索一个术语？

我想念什么？

Answer 1

用户NDM的评论解决了问题。因此，如果还不够清楚（如果有人发现我将来遇到的同一问题），这是我的最终工作控制器：

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'));
}