paginate() throws error while using having() while get() shows the results

Question

Code:

if ($request->going){
    if($request->lattitude && $request->longitude){
        $CurrentIplat = $request->lattitude;
        $CurrentIplon = $request->longitude;
        $ads1 = DB::table('ads')
        ->leftJoin('categories', 'ads.category_id', '=', 'categories.id')
        ->select(
            'ads.*','categories.category_name',
            DB::raw("( 6371 * acos( cos( radians('{$CurrentIplat}') ) * cos( radians( ads.Latitude ) ) * cos( radians( ads.Longtitute ) - radians({$CurrentIplon}) ) + sin( radians({$CurrentIplat}) ) * sin( radians( ads.Latitude ) ) ) ) AS distance")
                )
        ->where('ads.domain',$this->domain)
        ->groupBy('ads.id')
        ->orderBy('distance') 
        ->having('distance','<','500')
        ->get();
            }
        }

Gives proper output.

While using paginate()

if ($request->going){
    if($request->lattitude && $request->longitude){
        $CurrentIplat = $request->lattitude;
        $CurrentIplon = $request->longitude;
        $ads1 = DB::table('ads')
        ->leftJoin('categories', 'ads.category_id', '=', 'categories.id')
        ->select(
            'ads.*','categories.category_name',
            DB::raw("( 6371 * acos( cos( radians('{$CurrentIplat}') ) * cos( radians( ads.Latitude ) ) * cos( radians( ads.Longtitute ) - radians({$CurrentIplon}) ) + sin( radians({$CurrentIplat}) ) * sin( radians( ads.Latitude ) ) ) ) AS distance")
                )
        ->where('ads.domain',$this->domain)
        ->groupBy('ads.id')
        ->orderBy('distance') 
        ->having('distance','<','500')
        ->paginate(10);
            }
        }

It throws

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from `ads` left join `categories` on `ads`.`category_id` = `categories`.`id` where `ads`.`domain` = nz group by `ads`.`id` having `distance` < 5000)

When you add the paginate part, the select reset to ` count(*) as aggregate ` to make the query paginate and the select raw where you include the distance doesnt exists — Raúl Monge, Jul 24 '18 at 06:14
So, you mean select conditions do not work with paginate? I am confused. Can you please rephrase? — Keval Domadia, Jul 26 '18 at 04:18

Lovepreet Singh · Accepted Answer · 2018-07-26T09:29:02.033

paginate() function in Laravel, executes two queries:

First, to get records with given pagination limit like 10 in your case.
Second, to get total count of records according to given where conditions.

Problem is in second query where count(*) as aggregate is the only field selected and there is no field like distance to perform having operation.

Only get() function working because it executes only one query to get all records with given where conditions.

Solution: Use distance condition in where instead of having as it is more efficient. But here the same calculation of distance need to write again as where conditions do not work with alias. Code should be:

if ($request->lattitude && $request->longitude) {
    $CurrentIplat = $request->lattitude;
    $CurrentIplon = $request->longitude;
    $queryDistance = " ( 6371 * acos( cos( radians('{$CurrentIplat}') ) * cos( radians( ads.Latitude ) ) * cos( radians( ads.Longtitute ) - radians({$CurrentIplon}) ) + sin( radians({$CurrentIplat}) ) * sin( radians( ads.Latitude ) ) ) ) ";
    $ads1 = DB::table('ads')
            ->leftJoin('categories', 'ads.category_id', '=', 'categories.id')
            ->select('ads.*', 'categories.category_name', DB::raw("$queryDistance AS distance"))
            ->whereRaw('ads.domain = ' . $this->domain . " AND $queryDistance < 500")
            ->groupBy('ads.id')
            ->orderBy('distance')
            ->paginate(10);
}

I have updated my question to reflect the error that I see when I try this method. — Keval Domadia, Jul 26 '18 at 06:49

score 0 · Answer 2 · answered Jul 26 '18 at 06:59

When using paginate Laravel first launches

SELECT count(*)

query to get number of records, so because in this query you have also having('distance', '<', '500') obviously SQL doesn't know what is distance in this query. The solution here is either using manual pagination (more complex one) or to repeat distance definition twice:

$distance = "( 6371 * acos( cos( radians('{$CurrentIplat}') ) * cos( radians( ads.Latitude ) ) * cos( radians( ads.Longtitute ) - radians({$CurrentIplon}) ) + sin( radians({$CurrentIplat}) ) * sin( radians( ads.Latitude ) ) ) )";


if ($request->going){
    if($request->lattitude && $request->longitude){
        $CurrentIplat = $request->lattitude;
        $CurrentIplon = $request->longitude;
        $ads1 = DB::table('ads')
        ->leftJoin('categories', 'ads.category_id', '=', 'categories.id')
        ->select(
            'ads.*','categories.category_name',
            DB::raw($distance.' AS distance'))
        ->where('ads.domain',$this->domain)
        ->groupBy('ads.id')
        ->orderBy('distance') 
        ->having(DB::raw($distance),'<','500')
        ->paginate(10);
            }
        }

So as you see above distance column was extracted to $distance variable and then it's used twice - 1st time in select and 2nd time in having

paginate() throws error while using having() while get() shows the results

2 Answers2