yii2 gridview没有使用hasMany retionship显示所有左连接值

Model Search :

型号搜索:

$query = Countries::find()->joinWith(['states']);
$dataProvider = new ActiveDataProvider([
    'query' => $query,           
]);        

$dataProvider->setSort([
    'defaultOrder' => ['doc_date'=>SORT_DESC],
]);

if (!($this->load($params) && $this->validate())) {           
    return $dataProvider;
}

Model :

public function getStates()
{
    return $this->hasMany(States::className(), ['state_id' => 'state_id']);
}

I need result like

我需要结果

Id      Country     State
1       India       State 1
2       India       State 2
3       India       State 3
4       USA         USA State1
5       USA         USA State2

When I'm using gridview I'm getting following result

当我使用gridview时,我得到了以下结果

Id      Country     State
1       India       State 1
4       USA         USA State1

Please give solutions to fix this issue.

请提供解决方案来解决此问题。

4 个解决方案

#1

What you're seeing is the intended behavior: normally you wouldn't want your ActiveRecord query to contain duplicate primary records, so Yii filters out any duplicates caused by JOINs. You can see this behavior defined here: https://github.com/yiisoft/yii2/blob/master/framework/db/ActiveQuery.php#L220

你所看到的是预期的行为:通常你不希望你的ActiveRecord查询包含重复的主记录,所以Yii过滤掉由JOIN引起的任何重复。您可以在此处看到此行为:https://github.com/yiisoft/yii2/blob/master/framework/db/ActiveQuery.php#L220

Since what you want is essentially to display the raw results as generated by the SQL with a JOIN (one row for each combination of Country and State), I think the most pragmatic solution would be to use the SqlDataProvider instead of the ActiveDataProvider.

因为你想要的是显示由SQL生成的原始结果和JOIN(Country和State的每个组合一行),我认为最实用的解决方案是使用SqlDataProvider而不是ActiveDataProvider。

This should return exactly what you want:

这应该返回你想要的:

$query = Countries::find()->joinWith(['states'], false)->select(*);

$dataProvider = new SqlDataProvider([
    'sql' => $query->createCommand()->getRawSql(),           
]);

#2

If you explicitly specify the selected columns using the select() method, you can achieve the same result, without messing with raw sql queries

如果使用select()方法显式指定所选列,则可以获得相同的结果,而不会弄乱原始sql查询

$query->select(['countries.*','states.*']);

#3

The answer given by laszlovl works good, but needs to change the 'query' key value by 'sql' like below:

由laszlovl给出的答案效果很好,但需要通过'sql'更改'query'键值,如下所示:

$query = Countries::find()->joinWith(['states'], false)->select(*);

$dataProvider = new SqlDataProvider([
    'sql' => $query->createCommand()->getRawSql(),           
]);

From the Yii 2 Docs we can find that $sql property get the SQL statement to be used for fetching data rows. The default value of this property is "null"

从Yii 2 Docs中我们可以发现$ sql属性获取用于获取数据行的SQL语句。此属性的默认值为“null”

#4

well groupBy helped me

井井有缘帮我

check this out (hope it will help)

看看这个(希望它会有所帮助)

    $query = Post::find();
    $query->innerJoinWith(['userVotePosts'], true);

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

#1