未找到列：laravel中的execption

laawzig2 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(329)

我正在做一些维护项目，其中所有的都是sql查询，但我想把它转换成laravel。以下是查询：-

$members = Member::select('members.id','members.first_name',
                          'members.surname','members.username','members.password',
                          'members.email','user_access.active',
                          DB::raw('SUM( IF (user_access.active = "y", 1, 0) ) as acount'))
        ->join('user_access','user_access.member_id','=','members.id')
        ->where('special_access','=','n')
        ->groupby('user_access.member_id')
        ->having('acount','>','0')
        ->orderby('members.id','desc')
        ->orderby('members.username','ASC')
        ->orderby('user_access.note','DESC')
        ->paginate(30);

当我执行查询时，它给了我错误。在having子句的错误下面

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'acount' in 'having clause' (SQL: select count(*) as aggregate from `members` inner join `user_access` on `user_access`.`member_id` = `members`.`id` where `special_access` = n group by `user_access`.`member_id` having `acount` > 0)

mysql laravel laravel-query-builder laravel-5.5

来源：https://stackoverflow.com/questions/53722424/column-not-found-execption-in-laravel

1条答案

按热度按时间

gmol16391#

我想这样的事情会对你有帮助的。

$userAccess = DB::table('user_access')
    ->where('special_access','=','n')
    ->where('active','y')
    ->groupby('member_id');

Member::select([
    'members.id', 
    'members.first_name', 
    'members.surname', 
    'members.username', 
    'members.password', 
    'members.email', 
    'user_access.active'
])->joinSub($userAccess, 'user_access', function($join){
    $join->on('user_access.member_id', '=', 'members.id');
})->orderby('members.id','desc')
->orderby('members.username','ASC')
->orderBy('user_access.note','asc')
->paginate(30);

或

$members = Member::select([
    'members.id', 
    'members.first_name', 
    'members.surname', 
    'members.username', 
    'members.password', 
    'members.email', 
    'user_access.active'
])->join('user_access', function($join){
    $join->on('user_access.member_id', '=', 'members.id')->on('user_access.active', '=', DB::raw('"y"'));
})
    ->where('special_access','=','n')
    ->groupby('user_access.member_id')
    ->orderby('members.id','desc')
    ->orderby('members.username','ASC')
    ->orderby('user_access.note','DESC')
    ->paginate(30);

正如apokryfos在评论中所说：
分页器将尝试获取查询的计数，并在这样做导致此错误时从中删除所有选择。
如果你只需要记录 user_access.active = "y" 然后，您不需要首先选择它们，然后尝试通过 HAVING

赞(0）回复(0）举报 2021-06-17

我来回答

未找到列：laravel中的execption

1条答案

相关问题

热门标签

最新问答