Don't override the select or force the selection of all columns

[andylobel]

Ok so this line of code causes a few problems in quite a few scenarios.

return $q->select(DB::raw("*, ( $meanRadius * acos( cos( radians($lat) ) * cos( radians( $latColumn ) ) * cos( radians( $lngColumn ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( $latColumn ) ) ) ) AS distance"))

It needs to be changed to:

$q->addSelect(DB::raw("( $meanRadius * acos( cos( radians($lat) ) * cos( radians( $latColumn ) ) * cos( radians( $lngColumn ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( $latColumn ) ) ) ) AS distance"))

This will allow us to specify our own selects, e.g.

User::select('users.*')->within($radius, $radiusType, $lat, $lng)

Also including * in that forced select also breaks stuff, as if you join tables to the query above, it errors out usually because both tables have an id column.

Also if wanting to create a manual pagination, it is totally unnecessary to include every column when counting the results (especially if the query is really complicated and joins lots of tables).

---

A brief version of my code for creating paginated distance query (after modifying as above):

$page = $request->input('page', 1); // Get the ?page=1 from the url
$perPage = 8; // Number of items per page
$offset = ($page * $perPage) - $perPage;

$users = User::select('users.*')
    ->within($radius, $radiusType, $lat, $lng)
    ->join('user_profiles', 'user_profiles.user_id', 'users.id')
    ->join('search_prefs', 'user_profiles.user_id', '=', 'search_prefs.user_id')
    ->whereBetween(DB::raw('TIMESTAMPDIFF(YEAR, user_profiles.birth_date, CURDATE())'), [$searchPrefs->age_start, $searchPrefs->age_end] );

// take the count of the users without a limit (only really need to use this if your pagination includes numbers and not just prev/next)
$resultCount= User::select(DB::raw('count(*) as resultCount'))->from(DB::raw(
    sprintf( "( %s ) as users", $users->toSql())
))->mergeBindings($users->getQuery());

// now add the limit to the query
$users->skip($offset)->take($perPage);

$users = new LengthAwarePaginator(
    $users,
    $resultCount->pluck('resultCount')->first(),
    $perPage, // Items per page
    $page, // Current page
    ['path' => $request->url(), 'query' => $request->query()] // We need this so we can keep all old query parameters from the url
);

return view('search', ['users' => $users]);