How to properly filter (search) data in YII2 with multiple related tables?

Question

Yii

Migrations (database structure):
car services table:

$this->createTable('car_service', [
            'id' => $this->primaryKey(),
            'name' => $this->string(),
            'description' => $this->text(),
            'address' => $this->string(),
            'email' => $this->string(),
            'slug' => $this->string()->notNull()->unique()
        ], 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB');

service table:

$this->createTable('car_service_service', [
            'id' => $this->primaryKey(),
            'car_service_id' => $this->integer(),
            'service_id' => $this->integer(),
        ], 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB');

car models table:

$this->createTable('car_service_manufacturer', [
            'id' => $this->primaryKey(),
            'car_service_id' => $this->integer(),
            'manufacturer_id' => $this->integer(),
        ], 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB');

The car_service model implements a relationship between these tables:

public function getCarServiceManufacturers()
    {
        return $this->hasMany(CarServiceManufacturer::className(), ['car_service_id' => 'id']);
    }
    public function getCarServiceServices()
    {
        return $this->hasMany(CarServiceService::className(), ['car_service_id' => 'id']);
    }

Here is the SearchModel for CarService:

<?php

namespace app\models;

use yii\base\Model;
use yii\data\ActiveDataProvider;

/**
 * CarServiceSearch represents the model behind the search form of `app\models\CarService`.
 */
class CarServiceSearch extends CarService
{
    public $manufacturer;
    public $service;

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['id'], 'integer'],
            [['name', 'description', 'address', 'email', 'slug', 'manufacturer', 'service'], 'safe'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {
        $query = CarService::find()->joinWith(['carServiceManufacturers','carServiceServices']);

        // add conditions that should always apply here

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => 10,
            ],
        ]);

        $this->load($params);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

        // grid filtering conditions
        $query->andFilterWhere([
            'id' => $this->id,
            'manufacturer_id' => $this->manufacturer,
            'service_id' => $this->service
        ]);

        $query->andFilterWhere(['like', 'car_service.name', $this->name])
            ->andFilterWhere(['like', 'description', $this->description])
            ->andFilterWhere(['like', 'address', $this->address])
            ->andFilterWhere(['like', 'email', $this->email])
            ->andFilterWhere(['like', 'slug', $this->slug]);

        return $dataProvider;
    }
}

It generates a request:

SELECT COUNT(*) FROM `car_service` LEFT JOIN `car_service_manufacturer` ON `car_service`.`id` = `car_service_manufacturer`.`car_service_id` LEFT JOIN `car_service_service` ON `car_service`.`id` = `car_service_service`.`car_service_id` WHERE `manufacturer_id`='6'

in response to the request, 2 records come in, in which the same car service, but 2 records due to the fact that this service has 2 services provided.
because of this, apparently, all pagination falls.
Please tell me what am I doing wrong?

Answer 1

Solution:
add grouping by service table id.

public function search($params)
    {
        $query = CarService::find()->joinWith(['carServiceManufacturers','carServiceServices'])->groupBy('car_service.id');