Yii2 Adding SearchModel Many-to-Many relationships produces duplicative results

Question

I have a model that has six many-to-many relationships. When I add the joinWith entries in the SearchModel and, then, look at what gets returned in the index.php view (with no search parameters applied), it shows many more rows than are actually in the base model. Upon further inspection, I find that multiple duplicative rows are being returned. It also throws pagination way off. For instance, if I add just one joinWith in the SearchModel and I've got pagination set to 10 rows per page, here's what happens. In the base model which has 175 rows, the first page will show "1-4 of 425 items". The second page will show "111-16 of 425 items." and the last item of the first page is duplicated at the top of the second page. To give some background:

The base model has the following relationships:

/**
     * @return \yii\db\ActiveQuery
     */
    public function getHerbalHerbsHerbalPreparations()
    {
        return $this->hasMany(\common\models\HerbalHerbsHerbalPreparations::className(),
                              ['herbal_preparation_id' => 'id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getHerbalHerbs()
    {
        return $this->hasMany(\common\models\HerbalHerbs::className(), ['id' => 'herbal_herb_id'])
                    ->viaTable('herbal_herbs_herbal_preparations', ['herbal_preparation_id' => 'id']);
    } */

In the SearchModel, I've got this entry:

$query = HerbalPreparations::find()->joinWith('herbalHerbs');

Please note that it makes no difference if I add the optional joinWith parameters for eager loading and join type. `$query = HerbalPreparations::find()->joinWith('herbalHerbs', true, 'LEFT JOIN'); produces the same results.

Without any joinWith, the index.php view looks like this:

With the joinWith described above, page 1 of index.php looks like this:

And page 2 of the index.php view looks like:

Note the duplication of the last row on page 1 and the first row of page 2.

Needless to say, this problem becomes exponentially worse if I specify the additional many-to-many relations with joinWith.

If I actually enter a search parameter using the GridView filter or a search form, it works perfectly. The problem is that the index.php view needs to work properly with no search parameter.

This seems like it would be a common problem for anyone using Yii2 in any kind of advanced application. I'm sure I must be doing something wrong, but I searched Google until I'm exhausted and haven't been able to find anything helps. `

A quick solution could be implementing the ->joinWith('herbalHerbs'); only when the search input field is not empty — gmc
That sounds like it would make joinWith horribly complex just to do something that should be a standard, everyday, normative process needed by every Web developer on the planet. Surely, Yii2 has implemented a solution for such a totally common issue. When you add to that, that this would be ->joinWith('herbalHerbs', 'herbalAilments', 'herbalMedicinalUses', 'herbalProperties', 'herbalTags') in the final product, checking for each of those would be very complex indeed. — LarryTX

LarryTX LarryTX · Accepted Answer · 2017-04-13T06:25:08

I can't take responsibility for this answer. Actually, Newbie on the Yii forum gave this answer. It's actually rather simple — just add a groupBy('id'). For instance

$query = HerbalPreparations::find()->joinWith('herbalHerbs', true, 'LEFT JOIN')->groupBy(['id']);

I suppose I should have been able to figure this one out from a basic knowledge of SQL, but I've never had to use groupBy in this way.

This seems like such a common need that I would think it would be documented and found just about everyplace on a simple search. Unfortunately, it doesn't appear to be.

Yii2 Adding SearchModel Many-to-Many relationships produces duplicative results

1 Answers