Laravel 8 & Livewire - query not works

Question

I am making an application with Laravel 8 and Livewire. In my reportResidui.blade.php view I have integrated a livewire component called report-residui-header-filter and which I use to filter (this component works fine). The problem occurs when I perform the query, I need to filter from a table going to group the data for the field anagrafica_soggetto.codiceFiscale with a where clause where('importoResiduo', '>', '0'). But when I run the query I get the following error and I don't understand what it comes from, since removing the groupBy clause works.

Anyone have any suggestions or advice?

Error:

SQLSTATE[42000]: Syntax error or access violation:
1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tenantseed_prova_province.anagrafica_soggetto.denominazioneSoggetto'
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by (SQL: select distinct `denominazioneSoggetto`, `anagrafica_soggetto`.`codiceFiscale`, `indirizzoPOSTA`, `importoCarico` as `carico`,
`importoResiduo` as `residuo`, `pagatoNormale` as `riscosso`, `pagatoDiscarico` as `sgravio` from `anagrafica_soggetto` inner join `minuta_partita` on `minuta_partita`.`id_soggetto` = `anagrafica_soggetto`.`id` left outer join `users` on `minuta_partita`.`id_user` = `users`.`id` left outer join `partita_pagamenti` on `partita_pagamenti`.`id_minuta_partita` =
`minuta_partita`.`id` inner join `tipologia_imposta` on `minuta_partita`.`id_tipologia_imposta`
= `tipologia_imposta`.`id` where `importoResiduo` > 0 group by
`anagrafica_soggetto`.`codiceFiscale`)

Livewire Component with query:

<?php

namespace App\Http\Livewire;

use Livewire\Component;
use Illuminate\Http\Request;
use App\Models\MinutaPartita;
use App\Models\AnagraficaSoggetto;
use App\Models\TipologiaImposta;
use Illuminate\Support\Facades\DB;
use Livewire\WithPagination;
use App\Tenant;

class ReportResiduiHeaderFilter extends Component
{
    protected $paginationTheme = 'bootstrap';

    protected $connection = null;
    public $first_render = true;
    public $filtered = null;

    // Definiamo l'array dei filtri, e filtriamo se vogliamo raggruppare i filtri per CF del soggetto
    public $filter = [
        'group' => '' //indichiamo se raggruppare o meno (vuoto non si raggruppa, 1 si raggruppa per CF)
    ];

    public function mount(Request $request){
        
        if (null !== $request->get('throughMiddleware')) {
            $this->connection = 'tenant';
        } else {
            $this->connection = null;
        }

    }

    public function filtri(){

        if (null !== request()->get('throughMiddleware')) {
            $this->connection = 'tenant';
        } else {
            $this->connection = null;
        }

        $anagrafica = new AnagraficaSoggetto();
        $anagrafica->setConnection($this->connection);

        $pratiche = $anagrafica->select(
            'denominazioneSoggetto',
            'anagrafica_soggetto.codiceFiscale',
            'indirizzoPOSTA',
            'importoCarico as carico',
            'importoResiduo as residuo',
            'pagatoNormale as riscosso',
            'pagatoDiscarico as sgravio',
        )->distinct()
            ->join('minuta_partita', 'minuta_partita.id_soggetto', '=', 'anagrafica_soggetto.id')
            ->join('users', 'minuta_partita.id_user', '=', 'users.id', 'left outer')
            ->join('partita_pagamenti', 'partita_pagamenti.id_minuta_partita', '=', 'minuta_partita.id', 'left outer')
            ->join('tipologia_imposta', 'minuta_partita.id_tipologia_imposta', '=', 'tipologia_imposta.id');
        
        // recupero solo le pratiche che hanno residui non nulli
        $pratiche = $pratiche->where('importoResiduo', '>', 0);

        // if i remove this and i get the value by where clause it woks
        if ($this->filter['group'] != '') {
            $pratiche = $pratiche->groupBy('anagrafica_soggetto.codiceFiscale')->get();
        }

        dd($pratiche);
    }
    

    public function render()
    {
        return view('livewire.report-residui-header-filter');
    }
}

Answer 1

you are trying to group by anagrafica_soggetto.codiceFiscale but you have many joins in your query so you have to explicitly tell MySQL how to group the other selected items in your select list.

The solution is to group by at least 1 field from every table you are joining.