Yii2 how to remove duplicate records in $dataProvider when joinWith()?

Question

Yii

Hello everyone!) I just can’t deal with the data in the dataProvider. Perhaps this applies not only to him, but also to requests.
Essence such... I use model ProfileSearch in which I do joinWith of other tables with necessary data (city, region....).

$query = Profile::find();
        $query = $this->getTabs($query);

        $query->joinWith(['user', 'city', 'certifications', 'appointments.event'])
            ->leftJoin('auth_assignment','auth_assignment.user_id = profile.user_id');

They also sort and filter data.

// grid filtering conditions
        $query->andFilterWhere([
            'id' => $this->id,
            'user_id' => $this->user_id,
            'date_birthday' => $this->date_birthday,
            'city.id' => $this->city_id,
            'gender' => $this->gender,
            'category_id' => $this->category_id,
            'comitet_id' => $this->comitet_id,
            'user.status' => $this->status,
            'auth_assignment.item_name' => $this->type,
            'city.id_district' => $this->district_id,
            'city.id_region' => $this->region_id,
        ]);

I display all this using the ListView widget using dataProvider. After joinWith, the amount of data has increased. For example, there are 100 users in the system, and $dataProvider->totalCount shows 150. Well, this is understandable. After all, they made leftJoin of other data. However, another problem arose. When paginating pages, duplicate users appear.

Question:
1. How can I remove duplicate entries?

Answer 1

To avoid duplication, group by profile IDs
$query->groupBy('id');

Answer 2

When I was struggling with this problem, I decided to see how the professionals do it... I looked at the code of the site yiiframework.com, the news section or something similar, there are many-to-many tags for news... As a result... NOTHING! They also display everything incorrectly.
As a result, I just made a separate request, and then I mapped it myself. The problem with Yii2 is that if you don't follow what the framework allows, then you need to fight it, although so far everything is ok - everything is written very quickly.

Answer 3

Known problem of duplicates in pagination, in Yii 1 it's the same. MultiSort helps
$dataProvider = new ActiveDataProvider([
'query' => $query,
'sort' => [
'defaultOrder' => ['id' => SORT_DESC],
'enableMultiSort' => true,
],
]);