Pfthb

This is a query that I have in raw sql.

DB::select('SELECT bldgs.name as building , floors.name as floor, areas.name as area, locations.area_id ,count(reqs.location_id) as occupied FROM `reqs` '
 . 'JOIN locations ON locations.id = location_id '
 . 'JOIN areas ON areas.id = area_id '
 . 'JOIN floors ON floors.id = areas.floor_id'
 . ' JOIN bldgs ON bldgs.id = bldg_id '
 . 'WHERE `status`=2 and (DATE_FORMAT(start_date,"%Y-%m")<= "'.$dateFrom.'" AND DATE_FORMAT(end_date,"%Y-%m")>="'.$dateTo.'") group by locations.area_id, areas.name, floors.name, bldgs.name' );

And this is one of many attempts to make it work in Laravel elequent instead of raw.

Req::select('bldgs.name as building',DB::raw('count(location_id) as count_occupied')) 
 ->join('locations','locations.id','=','location_id')
 ->join('areas','areas.id','=','locations.area_id')
 ->join('floors','floors.id','=','areas.floor_id')
 ->join('bldgs as bl','bl.id','=','floors.bldg_id')
 ->where('reqs.status','=', '2') 
 ->where('start_date','<=', $date)
 ->where('end_date','>=', $date)
 ->groupBy('bldgs.name')

I need to understand why the second way gives mysql error and refuses to run the query above. Is is a mistake in my code or is this normally not possible in using eloguent to group by like this except in raw mysql string?

This is the error I get.

 SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bldgs.name' in 'field list' (SQL: select `bldgs`.`name` as `building`, count(location_id) as count_occupied from `reqs` inner join `locations` on `locations`.`id` = `location_id` inner join `areas` on `areas`.`id` = `locations`.`area_id` inner join `floors` on `floors`.`id` = `areas`.`floor_id` inner join `bldgs` as `bl` on `bl`.`id` = `floors`.`bldg_id` where `reqs`.`status` = 2 and `start_date` <= 2018-10 and `end_date` >= 2018-10 group by `bldgs`.`name`)

As the error states, you don't have a bldgs.name column. You named the bldgs table bl when you joined it.

Rename your references from bldgs.name to bl.name.