Search code examples
joingridviewyii2relation

Inconsistency between data displayed in Kartik's GridView and rows count - Yii2


In my GridView, the number of rows returned is not the same showed in the upper right corner. In the corner, the grid counts 7450 rows but in reality it returns 4351 rows.

Here is an example of wrong behavior: Screenshot In the example the Gridview renders 1 row (it is correct) but the summary in the corner says "Showing 1-1 of 4 items).

I have probably found what is causing the error by reading this post: Yii2 gridview showing more records than the table. This error also causes the pagination to be broken.
Unfortunately that solution didn't solve my problem. Or maybe I'm missing something.

My case
I need to display relational data in a Gridview so I have created this relation in my AuthorsProjectPpi Model:

public function getInstitutionName(){
    return $this->hasOne(AuthorsInstitution::className(), ['md_institution_tokens'=>'ppi_organization']);
}

The problem arises in the search function of the AuthorsProjectPpiSearch Model:

public function search($params)
{
    $query = AuthorsProjectPpi::find();
    $query->joinWith('institutionName');
    ...
}

That Join produces 7450 rows (and so the count of the rows in the upper right corner of the Gridview is 7450) but than the Gridview instantiates just 4351 rows (that is the number that I want).
If I comment the Join:

public function search($params)
{
    $query = AuthorsProjectPpi::find();
    //$query->joinWith('institutionName');
    ...
}

In the upper right corner is shown the correct number (4351), the column of the relation is also displayed correctly but I can't filter the results by that column.

I can't really fix this problem.
I'm using Kartik's Gridview if this can help.


Solution

  • Solved
    I just forgot to put a GroupBy to the query...

    public function search($params)
    {
        $query = AuthorsProjectPpi::find();
        $query->joinWith('institutionName')->groupBy('id');
        ...
    }
    

    That completely solved the problem.