时间戳

首页 > 解决方案 > 使用 group by 和 where 的 Laravel 查询不起作用

laravel - 使用 group by 和 where 的 Laravel 查询不起作用

问题描述

这是我在原始 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' );

这是使它在 Laravel elequent 中而不是 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')

我需要了解为什么第二种方式会出现 mysql 错误并拒绝运行上面的查询。是我的代码中的错误，还是在使用 eloguent 进行分组时通常不可能，除非在原始 mysql 字符串中？

这是我得到的错误。

 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`)

标签： laraveleloquent

解决方案

正如错误所述，您没有bldgs.name列。您在加入时命名了该bldgs表。bl

将您的引用从 重命名bldgs.name为bl.name。

推荐阅读