mysql - 找不到列 : execption in laravel

Question

我正在从事一些维护项目，其中所有都是 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)

Answer 1

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

$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);

As apokryfos said in the comments:

The paginator will attempt to get the count of the query and will remove all selects from it when doing so leading to this error.

如果您只需要带有 user_access.active = "y" 的记录，那么您不需要首先选择它们，然后尝试通过 HAVING 过滤掉它们>