Larval left joined Eloquent, forcing the id field to be overwritten

Question

I am creating an application for search ads using Laravel; the application should search for a business by distance (which I already built) and include the latest subscription to the business (if any), so I can order it and then the distance.

Two models are business and subscriptions. A business can have many subscribers (although only one of them will be 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 difficulty that I get is that the business.id field is overwritten by subscription.id . I did some searching and read that I turned on ->select('businesses.*') Before leftJoin should fix this, however, after 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 problem is that the left join seems to be getting the first record, I would like to get the most recent record (on created_at ). You can see that I already have the orderBy line in my controller, but this does not affect, because I see that it still returns the old record, even if a newer record is available.

Answer 1

If you use ->select('businesses.*') In your controller, you will rewrite your choice from scopeDistance() . You must use the table alias in the scope function. I will also remove groupBy() from the scope function, since it does not. And it is better to remove orderBy() , because you cannot overwrite it (easily) later.

 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('businesses.*')->addSelect($raw) ->having('distance', '<=', $distance); } 

If you want to order a result during the last time you create a subscription, you will need to order it using the aggregate function. In your case, this should be ORDER BY MAX(subscriptions.created_at) DESC . Also here you need a place GROUP BY business.id . And it makes no sense to use ORDER BY in a JOIN clause. I would expect to get an exception because I don't know how this could be translated into a valid SQL expression. But it seems that Laravel is simply ignoring this. So the code in your controller could be:

 $businesses = Business::distance($place['lat'], $place['lng'], $request->distance) ->ofShopType($request->shop_type) ->uptoBudget($request->price) ->leftJoin('subscriptions', 'businesses.id', '=', 'subscriptions.business_id') ->groupBy('business.id') ->orderByRaw('max(subscriptions.created_at) DESC') ->orderBy('distance', 'ASC'); return $businesses->get(); 

Answer 2

You are correct that using select('businesses.*') Will prevent overwriting the id field.

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

Answer 3

You can add the lastSubscription() relation to your Business model:

 public function lastSubscription() { return $this->hasOne(Subscription::class)->orderBy('created_at', 'desc'); } 

Then in your controller you can:

 return Business::distance($place['lat'], $place['lng'], $request->distance) ->ofShopType($request->shop_type) ->uptoBudget($request->price) ->with('lastSubscription') ->get(); 

Answer 4

I think you should not re-display $bussinesses var and just return the result of the query.

 return 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'); })->get();