php - Yii2 通过外部表字段和总数进行搜索

Question

我尝试通过外部表进行搜索，如下所示:

2 个表:

人员:

id
name
...

网址:

id
peopleID
url

People.php 模型:

public function getUrls()
{
    return $this->hasMany(Urls::className(), ['peopleID' => 'id'])->select(['url']);
}

PeopleSearch.php 模型:

...
$query->joinWith(['urls']);
...
$query
            ->andFilterWhere(['or',
                ['like', 'name', $this->name],
                ...
                ['like', 'url', $this->name]]
        );

这适用于在多个字段中搜索“名称”字段中输入的值，包括外国网址，但在我看来，我使用以下内容输入手动分页:

$dataProvider->prepare();
if ($dataProvider->totalCount > 0) 
    echo Yii::t('app', 'Showing').": <b> ".($dataProvider->pagination->page*$dataProvider->pagination->pageSize+1)."-".($dataProvider->pagination->page*$dataProvider->pagination->pageSize+$dataProvider->count)."</b> ".Yii::t('app', 'of')." <b>".$dataProvider->totalCount."</b> ".Yii::t('app', 'items');

else echo Yii::t('app', 'No results found.');

echo LinkPager::widget(['pagination' => $dataProvider->pagination])

并且 $dataProvider->totalCount 为我提供了连接表中的记录总数，但不是来自 people 的记录总数。例如，如果我在 people 表中有 2 条记录，并且每个记录在“url”表中有 20 个 url，index.php View 显示“显示 40 项中的 1-2 项”而不是“显示 1-2 项” 2 项”

此外，LinkPager::widget 显示的总页数错误

请注意，$dataProvider 从 Controller 传递到 View

$dataProvider = $searchModel->search(Yii::$app->request->queryParams);

我该怎么做才能使分页按照我想要的方式执行？

提前谢谢您，

Answer 1

在 People.php 模型中，我的建议是删除 ->select(['url']):

public function getUrls()
{
    return $this->hasMany(Urls::className(), ['peopleID' => 'id']);
}

这样您仍然可以在需要时操作这些网址。

在 PeopleSearch.php 模型中:

...
// $query->joinWith(['urls']);
// This line is the one that makes it so you get 20 results instead of 2, because you actually get one result for each url related to the people returned by the query.

$query->with(['urls']);
// This last line makes sure the model class populates the relation using only one query.
// Two queries will end up being executed to populate both the people and url models,
// however you will get the right amount for $dataProvider->totalCount.
...
if(strlen($this->url) > 0) {
    $urlsPeopleIDs = \app\models\Url::find()
        ->select('peopleID')
        ->asArray()
        ->where(['like', 'url', $this->url])
        ->all();

    $query->andWhere(['id' => $urlsPeopleIDs]);
}
// This way you will only filter by url when you receive a url string with lenght > 0.
// If you haven't already, you will need to create a public property called 'url' in your PeopleSearch.php model and add a 'string' or 'safe' rule so you can actually load it's value from post.