Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

stephen waweru's avatar

SQLSTATE[42000]: Syntax error or access violation: 1055 'projectdb.rentalhouse_sizes.rental_id' isn't in GROUP BY

i have a 3 tables rentals table,unit sizes tables and rental_units_pivot table.the rentals table contains the details of a rental,the unit sizes table contains a list of the units sizes such as i bedroom,2 bed room,etc.A rental facility might have more than one unit size.the unit sizes for each rental are saved in the pivot table. am trying to get the count of each unit size in the rental facility.for example 3 1 bedroom units and 2 1 bedroom units for rental A.I have been able to get the unit sizes for each rental rental now i want to get the count for each unit size in a rental in the pivot table and display them in a datatable.here is what i have done

$active_rentals=Rental_house::with([
            'rentalunits' => function($q){
                $q->Join('rentalhouse_sizes','rental_houses.id','=','rentalhouse_sizes.rental_id');
                $q->select('title');
                $q->select([DB::raw("count(size_id) as total"), "size_id"]);
                $q->groupBy('rentalhouse_sizes.size_id');
        }])
        ->where('rental_houses.status','=',1)
        ->select('rental_houses.id','rental_houses.name','rental_houses.address','rental_houses.image','rental_houses.status')
        ->orderBy('rental_houses.name','Asc')
        ->get(); ```

here is my rentalhouse_sizes pivot table structureenter image description here

in the rental house model i have this relationship between the rentals and the unit sizes

 function rentalunits(){
    return $this->belongsToMany(Rental_sizes::class,'rentalhouse_sizes','rental_id','size_id')->withPivot('status','amount');
}

i have tried joining the tables in a subquery but i get an error. how can i be able to count the number of unit size for each rental house?

0 likes
2 replies
stephen waweru's avatar

i solved by including all the pivot table columns in the groupby.

      ->with([
            'rentalunits' => function($q){
            $q->select("rental_sizes.title",DB::raw("count(rentalhouse_sizes.size_id) as total"), "rentalhouse_sizes.size_id");
            $q->groupBy('rentalhouse_sizes.size_id','rentalhouse_sizes.status','rentalhouse_sizes.amount','rental_sizes.title','rentalhouse_sizes.rental_id');
        }])
Tray2's avatar

@stephen waweru That is the proper solution, all fields you have in your select needs to be in the group by, except the aggregate field. There are some setting in the DB where you can turn that off, but that is a bad practice.

Please or to participate in this conversation.