yii2 gridview not showing all the left join values using hasMany retionship

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 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

Id      Country     State
1       India       State 1
4       USA         USA State1

Please give solutions to fix this issue.

score 5 · Accepted Answer · edited May 07 '18 at 13:45

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

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.

This should return exactly what you want:

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

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

Note: if you want to use the pagination feature, you must configure the [[yii\data\SqlDataProvider::$totalCount]] property to be the total number of rows (without pagination) . more : https://www.yiiframework.com/doc/guide/2.0/en/output-data-providers#sql-data-provider — sj59, Oct 25 '18 at 09:05

Edgar Cardona · Answer 2 · 2017-07-03T23:34:11.117

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

$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"

score 1 · Answer 3 · answered Mar 30 '17 at 20:00

1

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

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

answered Mar 30 '17 at 20:00

Szántó Zoltán

981
1
12
26

score 0 · Answer 4 · answered Dec 12 '18 at 14:15

0

well groupBy helped me

check this out (hope it will help)

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

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

answered Dec 12 '18 at 14:15

leila

461
1
7
21

score 0 · Answer 5 · answered Jul 18 '22 at 12:22

I tried many "solutions" and the only that really works - with minimal level of hacks - is to make view in the database. In PhpMyAdmin (https://www.404techsupport.com/2012/04/12/creating-and-editing-views-in-phpmyadmin/) one can make SELECT query, join as many tables you like, use AS to rename columns with identical names (id for example) and make view from it.

SELECT tbl1.*,tbl2.id as tbl2_id, tbl2.name as tbl2_name,(...)
FROM LEFT JOIN tbl2 ON tbl1.id = tbl2.id_tbl1 
WHERE 1

Then the procedure is almost identical like working with normal table. Exemptions are: one cannot update or insert into view and one MUST specify key column in the model (in my case id):

 public static function primaryKey($asArray = FALSE)
 {
    return ['id'];
 }

Then all search and filter commands and action column - all will work like charm. Just follow normal procedure to make the GridView (make YourtablelSearch.php etc.).

yii2 gridview not showing all the left join values using hasMany retionship

5 Answers5