Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – 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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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');
        }])

  2. 0

    When you do aggregations (i.e. COUNT()) you need to specify the groupings from your SELECT (including JOIN) case.

    So you’d have to include rentalhouse_sizes.rental_id in your groupBy for your query to work correctly due to the aggregation.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search