如何比较查询条件从BelongsToMany关联在CakePHP 4.x?

Question

我有一个工作岗位，类别和标签的关系。(帖子BelongsTo类别和BelongsToMany标签。)它们在我的视图和索引操作中工作得很好，没有任何问题。

现在，对于一个简单的"搜索"我正在使用查询生成器的功能。我设法使它成功地搜索与我的查询相关的帖子，只要将术语与帖子和类别中的字段进行比较，但我也想使它与标签一起工作。

这是我的(工作)控制器:

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]: Column not found: 1054 Unknown Column 'Tags.name' in 'where子句'"错误。

如果不是"$posts->where(…)"我使用"$posts->find('all'，['conditions' =>[…]]):"选择。

所以我被难住了…如何在一个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'));
}