CODEWITHSUNDEEP
mysqllaravellaravel-5.5laravel-query-builder
kunal
kunal

Reputation: 253

Column not found: execption in laravel

I am working on some maintaince project where all are sql queries but i want to convert it in to laravel. Here is the below query :-

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

Its giving me error when i execute the query. below the error in having clause

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)

Upvotes: 1

Views: 344

Answers (1)

Aria Radmand
Aria Radmand

Reputation: 331

I guess something like this will help you. 

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

OR

$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.

If you just need records with user_access.active = "y" then you do not need to select them in the first place and then try to filter them out by HAVING

Upvotes: 1

Related Questions