Yii2 gridview does not show all left join values ​​using hasMany relionship

Question

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 a 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 use gridview, I get the following result

 Id Country State 1 India State 1 4 USA USA State1 

Please provide solutions to fix this problem.

Answer 1

What you see is the intended behavior: usually you do not want your ActiveRecord request to contain duplicate primary records, so Yii filters out any duplicates caused by JOIN. You can see this behavior defined here: https://github.com/yiisoft/yii2/blob/master/framework/db/ActiveQuery.php#L220

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

This should return exactly what you want:

 $query = Countries::find()->joinWith(['states'], false)->select(*); $dataProvider = new SqlDataProvider([ 'sql' => $query->createCommand()->getRawSql(), ]); 

Answer 2

The answer given by laszlovl works well, but you need to change the value of the query key to 'sql', as shown below:

 $query = Countries::find()->joinWith(['states'], false)->select(*); $dataProvider = new SqlDataProvider([ 'sql' => $query->createCommand()->getRawSql(), ]); 

From the Yii 2 Docs, you can find that the $ sql property gets the SQL statement that will be used to retrieve the rows of data. The default value of this property is "null"

Answer 3

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

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

Answer 4

well groupBy helped me

check it out (hope this helps)

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