SearchModel Many-to-Many Relationships

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', true, 'LEFT JOIN');



The attached Yii2 without joinWith.png shows what index.php looks like with no joinWith specified. The attached Yii2 with joinWith.png shows what the index.php looks like with the single joinWith specified. 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.

Hi LarryTX, I think you should add a groupBy([‘id’]) in your $query to make sure you get no duplicate rows of your original model. When you use LEFT JOIN (or in this case joinWith) you may get duplicated rows because one element in the original table may be related to many elements in the other table (since it’s a many-to-many relation after all).

One way to get only one element from the "original" table is using groupBy as i mentioned above. Something like this:




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



Make sure you show all the data from your relations in one row though. Otherwise you’ll be losing data in your gridview.

I’m a bit confused.

[list=1]

[*]What do you mean by "Make sure you show all the data from your relations in one row."?

[*]Do I add the groupBy to each of the relationships? Remember that I have six many-to-many relationships in this model. Do I add the groupBy to each relationship?

[/list]

Please forgive my dumb questions. I figured it out, and your resolution worked perfectly! Now, I have to wonder why that information doesn’t figure prominently in the explanations on how to set this up. (It didn’t appear in any of the multitudes of resources that I looked on on how to set up the query in the SearchModel. In fact, other than setting up filters in GridView, there is an absolute paucity of information anywhere to be found. Certainly not in the yiiframework.com information on setting up searches.)

Don’t know how’s the code for the models involved in your case. Let’s say your original model where you defined all the relations you showed before it’s called Herbs. A many-to-many relation will return an array of models. Herbs may be related with many HerbalHerbs, so if you want to display the related attributes in your gridview, you have a few options at your disposal, you can for example:

1.- Display each individual row from the relation (display "duplicate" rows).




Herb.name | HerbalHerbs.name

HerbA       HerbalHerbsA

HerbA       HerbalHerbsB

HerbB       HerbalHerbsC

HerbB       HerbalHerbsA



2.- Display only one row from the "original" table (display gridview with no "duplicates").




Herb.name | HerbalHerbs.name

HerbA       HerbalHerbsA, HerbalHerbsB

HerbB       HerbalHerbsC, HerbalHerbsA



Regarding point 2.

I think you should add groupBy in your searchModel where $query is defined so you only modify the results for your gridview (since $dataProvider is built with $query, assuming your using searchModel similar to what the gii CRUD generator gives you).

Didn’t see your reply till now, glad you got it working.

Finding this kind of information can be a bit tricky sometimes, since it’s kind of use-case dependent. The forum is a pretty good place to solve this and similar issues though, highly recommend checking it whenever you can.

This is what I ended up doing, but I’m sure there’s a more concise, expeditious way. I’d be grateful for any suggestions.




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

            $query->joinWith('herbalForms', true, 'LEFT JOIN')->groupBy(['id']);

            $query->joinWith('herbalAilments', true, 'LEFT JOIN')->groupBy(['id']);

            $query->joinWith('herbalMedicinalUses', true, 'LEFT JOIN')->groupBy(['id']);

            $query->joinWith('herbalTags', true, 'LEFT JOIN')->groupBy(['id']);



You could do it like this:




$query = HerbalPreparations::find()->joinWith('herbalHerbs', true, 'LEFT JOIN')

            ->joinWith('herbalForms', true, 'LEFT JOIN')

            ->joinWith('herbalAilments', true, 'LEFT JOIN')

            ->joinWith('herbalMedicinalUses', true, 'LEFT JOIN')

            ->joinWith('herbalTags', true, 'LEFT JOIN')->groupBy(['id']);



or




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



Should be fine as well (haven’t tested the latter though).