can't groupBy when using BelongsToMany (Postgres)
Hi, the shouldSelect() function from BelongsToMany use aliasedPivotColumns() which merge the pivot columns in the SELECT statement:
protected function shouldSelect(array $columns = ['*'])
{
if ($columns == ['*']) {
$columns = [$this->related->getTable().'.*'];
}
return array_merge($columns, $this->aliasedPivotColumns());
}
Consider contracts() to be a BelongsToMany relationship with a pivot table. If I do $user->contracts()->join(some table)->groupBy('contracts.id'). Laravel returns an error saying I need to include all fields from the pivot table in the groupBy(), breaking the purpose of the groupBy(). After checking, Laravel generate an intermediate request using the shouldSelect function (adding the pivot fields in the select).
Since the doc offers no other way to remove those pivot fields from the select statement (the withPivot([]) function is only additive, it doesn't replace the select statement), groupBy() is unusable with postgres (stricter than mariadb) and BelongsToMany relationships (and probably other relationships).
I tried using DISTINCT ON for postgres, but then DISTINCT ON doesn't allow to do a "normal" sql ordering and fixes for this issue break the use of relations functions and paginations .... etc etc.
I opened an issue on GitHub suggesting to modify the souldSelect() or to offer a "replacive" withPivot([]) function, but it was closed requesting me to check here first that it is a real issue.
Am I missing something ? Thanks
Please or to participate in this conversation.