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.
3 Answers
Answers 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); } Answers 2
You could add a lastSubscription() relation to your Business model:
public function lastSubscription() { return $this->hasOne(Subscription::class)->orderBy('created_at', 'desc'); } Then in your controller you'd be able to do:
return Business::distance($place['lat'], $place['lng'], $request->distance) ->ofShopType($request->shop_type) ->uptoBudget($request->price) ->with('lastSubscription') ->get(); Answers 3
If you use ->select('businesses.*') in your controller you will will overwrite your select from scopeDistance(). You should use the table alias in the scope function. I would also remove groupBy() from the scope function, since it doesn't make sence there. And it's better to remove orderBy() as well, because you can't 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 the result by the last subscriptions creation time, you will need to order using an aggregate function. In your case it should be ORDER BY MAX(subscriptions.created_at) DESC. Also here is the right place to GROUP BY business.id. And it doesn't make sence to use ORDER BY in the JOIN clause. I'd expect to get an exception, because I don't know how that could be translated to a valid SQL expression. But it seems that laravel is just ignoring it. 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(); 
0 comments:
Post a Comment