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

Hello world!) I can not deal with the data in the dataProvider. Perhaps this applies to not only him, but also requests.

Here's the thing... I use the model ProfileSearch which makes joinWith other tables with the 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');

For it is also possible to 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,
 ]);


All this I deduce in using the widget ListView using dataProvider. After joinWith the amount of data increased. For example, users in the system 100, and $dataProvider->totalCount shows 150. Well, this is understandable. Did leftJoin other data. However, there was another problem. When these pages appear to duplicate users.

5bbe8ec3e6ed1961393094.png

Question:
1. How do I remove duplicate entries?
March 12th 20 at 08:11
4 answers
March 12th 20 at 08:13
Solution
To avoid duplicates, you have to group by ID profiles
$query->groupBy('id');
And true)) something I didn't think about it. I thought it was related to the data provider, and when I received the response below, do realize that this is "normal"))) - seth62 commented on March 12th 20 at 08:16
@seth62, I didn't neither, but interestingly, the creators of the website framework is also not thought. - annabel.Paucek74 commented on March 12th 20 at 08:19
@annabel.Paucek74, apparently not so advanced haha - seth62 commented on March 12th 20 at 08:22
@seth62, well, there is among the team just has a very cool dude, just a site someone made and not soared. - annabel.Paucek74 commented on March 12th 20 at 08:25
@annabel.Paucek74, of course, in my words, sarcasm))))))) - seth62 commented on March 12th 20 at 08:28
@seth62, the fact that this is completely normal, and the command framework here and. It is sometimes useful to get a plain query of ActiveQuery and execute it in any client, the person will see the results of the query, and there you can try to solve problems.
To plain request, you can:
$query->createCommand()->rawSql - ottilie_Krajcik90 commented on March 12th 20 at 08:31
@ottilie_Krajcik90, thank you. Know about this team! - seth62 commented on March 12th 20 at 08:34
March 12th 20 at 08:15
Solution
But then he gives all the unique values... No? That is, it will be the same - seth62 commented on March 12th 20 at 08:18
I believe that user and profile have a relationship 1k1
Why not get all records from the user profile jaunam? - marcia.Jacobs commented on March 12th 20 at 08:21
$query = Users::find()
->distinct()
->joinWith(['user', 'city', 'certifications', 'appointments.event'])
->leftJoin('auth_assignment','auth_assignment.user_id = id');
- marcia.Jacobs commented on March 12th 20 at 08:24
@marcia.Jacobs, Yes. 1 to 1) it works! Solved! - seth62 commented on March 12th 20 at 08:27
March 12th 20 at 08:17
When I suffered with this problem, I decided to see how the pros do it... Looked at the site code yiiframework.com, news section or something similar, there are tags to news many to many... In the end... NO way! They're also not displayed correctly.

I eventually just did a separate query, and then he hands mapel. The problem is Yii2 that if you do not go by what the framework allows, then you need to deal with it, but so far everything is OK - all very quickly written.
The above solution) - seth62 commented on March 12th 20 at 08:20
March 12th 20 at 08:19
A known problem of duplicates with pagination in Yii 1 is the same thing. MultiSort Helps

$dataProvider = new ActiveDataProvider([
'query' => $query,
'sort' => [
'defaultOrder' => ['id' => SORT_DESC],
'enableMultiSort' => true,
],

]);

Find more questions by tags Yii