How do I use pagination with having in laravel?

        $dbraw = [
        'schools.*'
    ];
    $sorttype = 'desc';



    $search= array();
    $school = School::groupBy('schools.id');
if (request('sort2') == 1){
    $sorttype = 'desc';
}
if (request('sort2') == 2){
        $sorttype = 'asc';
}
  //  $school->orderBy('schools.boost','asc');
    if (!request('sort2')){
    $school->orderBy('schools.boost','desc');
}
    if (request('cities')){
        array_push($search,['schools.city_id','=',request('cities')]);
    }
    if (request('counties')){
        array_push($search,['schools.county_id','=',request('counties')]);
    }
    if (request('schooltype1')){
        array_push($search,['schools.type','=',request('schooltype1')]);
    }
    if (request('schooltype2')){
        array_push($search,['schools.typet','=',request('schooltype2')]);
    }
    if (request('keyword')){
        array_push($search,['schools.name','like','%'.request('keyword').'%']);
    }
    if (request('point') || request('sort1') == 1){
      //  array_push($search,['school_points.status','=',1]); // Arama geldiğinde sorgu çalışsın 0 var diye
       // array_push($search,['school_points.status','=','1']);
        array_push($dbraw,DB::raw('avg(COALESCE(school_points.point,0)) as point'));
        array_push($dbraw,DB::raw('COALESCE(school_points.status,1) as pointstatus'));
        $school->leftJoin('school_points','school_points.school_id','=','schools.id');
        $school->havingRaw('point >= '.request('point'));
        $school->havingRaw('pointstatus = 1');
      if (request('sort1') == 1){           $school->OrderBy('point',$sorttype);      }
    }
    if (request('rating')  || request('sort1') == 2){
        //array_push($search,['school_comments.status','=',1]);
        array_push($dbraw,DB::raw('COALESCE(school_comments.status,1) as commentstatus'));
        array_push($dbraw,DB::raw('avg(COALESCE(school_comments.rating,0)) as rating'));
        $school->leftJoin('school_comments','school_comments.school_id','=','schools.id','left outer');
        $school->havingRaw('rating >= '.request('rating'));
        $school->havingRaw('commentstatus = 1');
        if (request('sort1') == 2){              $school->OrderBy('rating',$sorttype);    }
    }
    if (request('properties')){
        array_push($dbraw,DB::raw('count(DISTINCT(property_schools.id)) as property_count'));
        $school->leftJoin('property_schools', 'property_schools.school_id', '=', 'schools.id');
        $school->whereIn('property_schools.property_id',request('properties'));
        $school->havingRaw('property_count  >= '.count(request('properties')));
    }
    if (request('sort1') == 3){
        $school->orderBy('schools.admin_select',$sorttype);
    }
    if (request('sort1') == 4){
        $school->orderBy('schools.count',$sorttype);
    }
    if (request('sort1') == 5){
        $school->orderBy(DB::raw('RAND()'),$sorttype);
    }
       array_push($search,['schools.status','=',1]);



     $school->where($search);
     $school->select($dbraw);
 return $school->paginate(15);

Pagination without pagination works correctly but I don’t know a way to make filters without having it, can you help? I know my codes are a bit complicated and long. whereRaw i need to do i guess can you help? I use mysql and laravel

message: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'point' in 'having clause' (SQL: select count(*) as aggregate from `schools` left join `school_points` on `school_points`.`school_id` = `schools`.`id` where (`schools`.`status` = 1) group by `schools`.`id` having point >= 7 and pointstatus = 1)"

does not allow because of the alias, but like I can’t do without the alias

sqlto print

select `schools`.*, avg(COALESCE(school_points.point,0)) as point, COALESCE(school_points.status,1) as pointstatus, COALESCE(school_comments.status,1) as commentstatus, avg(COALESCE(school_comments.rating,0)) as rating, count(DISTINCT(property_schools.id)) as property_count from `schools` left join `school_points` on `school_points`.`school_id` = `schools`.`id` left join `school_comments` on `school_comments`.`school_id` = `schools`.`id` left join `property_schools` on `property_schools`.`school_id` = `schools`.`id` where `property_schools`.`property_id` in (?, ?) and (`schools`.`status` = ?) group by `schools`.`id` having point >= 1 and pointstatus = 1 and rating >= 1 and commentstatus = 1 and property_count >= 2 order by `schools`.`boost` desc

Answer

Pl change paginate(10) with simplePaginate(10)

Let me know if doesn’t work.

You can try this package: https://github.com/justbetter/laravel-pagination-with-havings

Advertisement

Answer