Larval left join with Eloquent causing id field to be overwritten

Question

I am building search listings application using Laravel; the app needs to search for Businesses by distance (which I've already built) and include the latest subscription to a business (if any) so I can order by it and then distance.

The two models here are Business and Subscriptions. A Business can have many Subscriptions (although there will only ever be one thats active).

Controller

$businesses = Business::distance($place['lat'], $place['lng'], $request->distance)
                        ->ofShopType($request->shop_type)
                        ->uptoBudget($request->price)
                        ->leftJoin('subscriptions', function($join) {
                            $join->on('businesses.id', '=', 'subscriptions.business_id')
                                 ->orderBy('subscriptions.created_at', 'desc');
                        });
return $businesses = $businesses->get();

Business Model

public function scopeDistance($query,$from_latitude,$from_longitude,$distance)
{
    $raw = \DB::raw('ROUND ( ( 3959 * acos( cos( radians('.$from_latitude.') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('.$from_longitude.') ) + sin( radians('.$from_latitude.') ) * sin( radians( latitude ) ) ) ), 1 ) AS distance');
    return $query->select('*')->addSelect($raw)
             ->orderBy( 'distance', 'ASC' )
             ->groupBy('businesses.id')
             ->having('distance', '<=', $distance);
}

So the difficulties I am getting are that the business.id field is being overwritten by the subscription.id. I've done some searching and have read that including ->select('businesses.*') before the leftJoin should resolve this, however by doing this I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column
      'business.distance' in 'having clause'
      (SQL: select `businesses`.*
          from `businesses`
          left join `subscriptions`
                   on `businesses`.`id` = `subscriptions`.`business_id`
          where `shop_type_id` = 1
            and `min_cost` <= 100
          group by `distance`, `businesses`.`id`
          having `distance` <= 50
          order by `distance` asc)

The second issue is that the left join seems to get the first record, I would like to get the most recent record (by created_at). You can see that I already have an orderBy line in my controller but this has no affect as I can see its still returning an older record even though a newer record is available.

Answer 1

You're correct that using select('businesses.*') will prevent the id field from being overwritten.

I would suggest using selectRaw as follows:

public function scopeDistance($query, $from_latitude, $from_longitude, $distance)
{
    $raw = \DB::raw('ROUND ( ( 3959 * acos( cos( radians('.$from_latitude.') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('.$from_longitude.') ) + sin( radians('.$from_latitude.') ) * sin( radians( latitude ) ) ) ), 1 ) AS distance');
    return $query->selectRaw("businesses.*, $raw")->orderBy( 'distance', 'ASC' )->groupBy('businesses.id')->having('distance', '<=', $distance);
}