Как правильно фильтровать (искать) данные в YII2 с несколькими связанными таблицами?

Question

[Дмитрий]

Миграции (структура БД):
таблица автосервисов:

$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');

таблица оказываемых услуг:

$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');

таблица моделей авто:

$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');

В модели car_service реализована связь между этими таблицами:

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

Вот SearchModel для 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;
    }
}

Она генерирует запрос:

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'

в ответ на запрос приходит 2 записи, в которых один и тот же автосервис, но записи 2 из-за того, что у этого сервиса 2 оказываемых услуги.

из-за этого, судя по всему, валится вся пагинация.
Подскажите пожалуйста, что я делаю не так?

Answer 1

[Дмитрий]

Решение:
добавить группировку по id таблицы сервисов.

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