Как правильно добавить groupBy в запрос?

Question

[Сергей Хлопов]

Здравствуйте, подскажите пожалуйста, на сайте есть категории, а у категорий есть характеристики, у некоторых категорий бывает что какая то часть характеристик схожа, и мне нужно убрать эти дубликаты, то есть оставить у категорий только уникальные характеристики, и хотелось бы это сделать запросом к БД а не в контроллере с помощью php.
В итоге я делаю такой вот запрос к БД:

$categories = Category::whereIn('id',$request->input('categories'))->with(['characteristics' => function($query) {
            $query->groupBy('id');
        }])->get();

laravel генерируют такой вот запрос:

select `characteristics`.*, `category_characteristic`.`category_id` as `pivot_category_id`, `category_characteristic`.`characteristic_id` as `pivot_characteristic_id` from `characteristics` inner join `category_characteristic` on `characteristics`.`id` = `category_characteristic`.`characteristic_id` where `category_characteristic`.`category_id` in (51, 52) group by `id`

И в итоге получаю следующую ошибку:

Ошибка в запросe (1055): Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'rqt1mvsaw_js3ht.category_characteristic.category_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Но если убираю $query->groupBy('id'); то запрос выполняется корректно и в итоге получаю следующий результат, на скриншоте красным выделил дубликат характеристики в двух категориях

Подскажите пожалуйста, как можно решить данную проблему ? Заранее благодарю за ответ.

Comments

[Максим Федоров]

не хватает перед полем, по которому группируете, алиаса

я не знаю синтаксис ларки, потому возможно может быть там просто указать связь,попробуйте так:
$query->groupBy('characteristics.id');

Answer 1

[Александр Филиппенко]

Это правильное поведение базы для такого запроса. Чтобы избежать двусмысленности, в случае если используется группировка, то либо можно в селекте использовать только поля из группировки, либо использовать агрегатные функции, либо колдовать с подзапросами. Чтобы основной запрос был валидным, с учётом агрегации, а его результаты использовать для получения остальных данных с помощью JOIN или WHERE IN
В вашем случае, если вы хотите всё же использовать Eloquent, нужно определить минимально необходимый набор данных и запросить только их. Что нужно список id и названий?

Comments

[Сергей Хлопов]

Благодарю вас за ответ, да, но ещё правда нужно changes_price и type_id получать

[Александр Филиппенко]

Сергей Хлопов, я вижу что у вас джоины, но не видя полной структуры таблиц, подсказать точнее не смогу

[Сергей Хлопов]

Александр Филиппенко, понял, ну у меня есть таблица characteristics и categories между ними связь многое-ко-многим, связующая таблица category_characteristic
Структура таблицы characteristics

А это структура таблицы categories

Правда там ещё вот в таблице characteristics ещё одна связь есть, внешний ключ type_id, для того что бы тип характеристики указать.

[Сергей Хлопов]

Сейчас в общем попробовал такой вот запрос сделать:

select `characteristics`.*
from `characteristics` 
inner join `category_characteristic` on `characteristics`.`id` = `category_characteristic`.`characteristic_id` 
where `category_characteristic`.`category_id` in (51, 52) 
group by `characteristics`.`id`

И сработало вроде, то есть если из select убрать всё и оставить только `characteristics`.* то работает:

Но только laravel то генерирует такой select:

select `characteristics`.*, `category_characteristic`.`category_id` as `pivot_category_id`, `category_characteristic`.`characteristic_id` as `pivot_characteristic_id`

И я так понимаю что мне нужен category_characteristic`.`category_id` что бы знать из какой категории характеристика,
в общем сложно как то =) в SQL я ещё не очень

[Александр Филиппенко]

Сергей Хлопов, а модель для связки есть? Как она описана?

[Сергей Хлопов]

Александр Филиппенко, в модели Category делал метод characteristics

/* Получает характеристики категории */
public function characteristics()
{
   return $this->belongsToMany('App\Models\Admin\Characteristic');
}

Ну и потом с помощью миграции создал связующую таблицу category_characteristic
Миграция вот:

Schema::create('category_characteristic', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedMediumInteger('category_id');
            $table->unsignedMediumInteger('characteristic_id');
            $table->foreign('category_id')->references('id')->on('categories');
            $table->foreign('characteristic_id')->references('id')->on('characteristics')->onDelete('cascade');
        });

[Александр Филиппенко]

Сергей Хлопов, при таком подходе решить чисто через ORM наверно не получится. Либо использовать \DB::select(); с прямым запросом который у вас рабочий получился, либо для таблицы связей определить модель, связь будет https://stackoverflow.com/questions/35306242/larav... такого вида.
соответственно можно будет от этой связующей модели ОРМ запрос построить

[Сергей Хлопов]

Александр Филиппенко, понял, благодарю вас, буду пробовать тогда

[vism]

(Facepalm)

$categories = Category::whereIn('id',$request->input('categories'))->with(['characteristics' => function($query) {
            $query->groupBy('characteristics.id');
        }])->get();

и выкл. only_full_group_by

Но нет, ёжики будут делать через raw sql ))))

Answer 2

[vism]

Всегда отключаю only_full_group_by :)

Щас хэйтеры налетят, но прописывать десятки полей это жесть, и потом поддерживать если структура таблиц меняется

Comments

[Сергей Хлопов]

Благодарю вас за ответ =)