yii2中的最佳匹配结果查询

dz6r00yl 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(375)

我有一个名为books的表，希望在books视图中显示“相关图书”。

books table
`book_id`
`isbn`
`book_name`
`author_name`
`Date`
`number`
`language`
`Translator`
`abstract`
`picture`
`created_at`

控制器代码

public function actionView($id)
{
    $model=$this->findModel($id);
    $dataProvider=$this->related($model);
    return $this->render('view', [
        'model' => $model,
        'dataProvider' => $dataProvider,
    ]);
}
protected function related($model)
{
    $query = Books::find();
    $query->select('book_id,picture, book_name,author_name,(( author_name LIKE \'%'.$model->author_name.'%\')+( book_name LIKE \'%'.$model->book_name.'%\')) as total')
    ->orFilterwhere(['or like','author_name' , explode(' ',$model->author_name)])
    ->orFilterwhere(['or like','book_name' , explode(' ',$model->book_name)])
    ->orderBy('total')
    ->limit(25);

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

问题：书名 LIKE \'%'.$model->book_name.'%\' 未接受数组。像这样的 LIKE \'%'.explode(' ',$model->book_name).'%\' 它必须是一个数组才能正常工作。如何在选择条件下使用数组。或者告诉我其他的方法来获得最好的匹配结果。tnx公司

mysql php yii2

来源：https://stackoverflow.com/questions/49915930/best-match-result-query-in-yii2

2条答案

按热度按时间

mnowg1ta1#

首先，如果你想完全不需要写在选择条件

$query->select('book_id,picture, book_name,author_name,(book_name+author_name) as total');
``` `$model->author_name` 以及 `$model->book_name` 是数组，所以您可以执行以下操作。

protected function related($model)
{
$query = Books::find();
$query->select('book_id,picture, book_name,author_name,(book_name+author_name) as total');

 $author_names=  explode(' ',$model->author_name);
 foreach($author_names as $author_name)
 {
  $query->orFilterwhere(['like','author_name' , $author_name]);
 }

 $book_names=explode(' ',$model->book_name);
 foreach($book_names as $book_name)
 {
  $query->orFilterwhere(['like','book_name' , $book_name]);
 }
->orderBy('total')
->limit(25);

$dataProvider = new ActiveDataProvider([
    'query' => $query,
]);

}

赞(0）回复(0）举报 2021-06-26

mwkjh3gx2#

您需要使用mysql match（）against（）特性（如果您使用的是不同的数据库，则使用替代项）。

$query = Books::find();
$query->select([
    'book_id,picture', 'book_name', 'author_name',
    new Expression('MATCH (author_name) AGAINST (:author_name) + MATCH (book_name) AGAINST (:book_name) AS total', [
        'author_name' => $this->author_name,
        'book_name' => $this->book_name,
    ]),
]);

if (!empty($this->author_name)) {
    $query->andWhere(new Expression('MATCH (author_name) AGAINST (:author_name)', [
        'author_name' => $this->author_name,
    ]));
}
if (!empty($this->book_name)) {
    $query->andWhere(new Expression('MATCH (book_name) AGAINST (:book_name)', [
        'book_name' => $this->book_name,
    ]));
}
$query->orderBy('total DESC')
    ->limit(25);

$dataProvider = new ActiveDataProvider([
    'query' => $query,
]);

赞(0）回复(0）举报 2021-06-25

我来回答

yii2中的最佳匹配结果查询

2条答案

相关问题

热门标签

最新问答