How to correctly add groupBy to the query?

Question

Laravel

Hello, please tell me, there are categories on the site, and categories have characteristics, for some categories it happens that some part of the characteristics is similar, and I need to remove these duplicates, that is, leave categories with only unique characteristics, and I would like to do this with a request to db and not in controller using php.
As a result, I make the following query to the database:

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

laravel generate the following request:

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`

And in the end I get the following error:

Ошибка в запрос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

But if I remove it, the request is executed correctly and as a result I get the following result, in the screenshot I highlighted the duplicate of the characteristics in two categories in red. Please tell me, how can I solve this problem? Thank you in advance for your response. $query->groupBy('id');

Answer 1

This is the correct base behavior for such a query. To avoid ambiguity, if grouping is used, then either you can use only fields from the grouping in the select, or use aggregate functions, or conjure with subqueries. In order for the main query to be valid, taking into account aggregation, and its results to be used to obtain the rest of the data using JOIN or WHERE IN
In your case, if you still want to use Eloquent, you need to define the minimum required data set and query only them. What do you need a list of id and titles?

Answer 2

I always turn off only_full_group_by :)
Right now, haters will swoop in, but it's hard to prescribe dozens of fields, and then support if the table structure changes