0

I am creating an application with Laravel to search businesses by geolocation. I have two models which my eloquent query needs to look at: Businesses and Subscriptions. A business could have many subscriptions (usually only one active at a time), the subscription model contains a end_date which I would like to include in my results and order them by it (DESC).

I have already built the geolocation search query scope within my 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('distance', 'id')->having('distance', '<=', $distance);
}

I have tried to incorporate this with a left join for related subscriptions:

$businesses = Business::distance($place['lat'], $place['lng'], $request->distance)
                                ->leftJoin('premium_subscriptions', function($join) {
                                    $join->on('businesses.id', '=', 'premium_subscriptions.business_id');
                                })

but I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in group statement is ambiguous

What I am trying to achieve is a returned list of businesses within my geolocation parameters but also an retrieve the latest subscription for that business (if any, by newest end_date value) and include subscription.end_date in my returned results to display them by furthest end_date in descending order.

I'd appreciate if someone could advise the best method to do this.

jedrzej.kurylo
  • 39,591
  • 9
  • 98
  • 107
InvalidSyntax
  • 9,131
  • 20
  • 80
  • 127
  • try to replace `groupBy('distance', 'id')` for `groupBy('distance', '{tableName}.id')` – AgeValed Apr 17 '17 at 23:57
  • While this does fix that error, a new error now occurs `Syntax error or access violation: 1055 Expression #19 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysite.subscriptions.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – InvalidSyntax Apr 18 '17 at 00:02
  • 1
    try with http://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error may fix that issue – AgeValed Apr 18 '17 at 00:04
  • Okay so we're getting closer now, two new issues now. So results are shown, there are businesses that have their own subscription records within the scope. However only the first record contains its subscription data, the second has null values. Both records also have null values for the business.id fields for some reason too. – InvalidSyntax Apr 18 '17 at 00:24
  • If you have a follow up question, then ask it in a new question, not in a comment. Although turning off strict mode just hodes the fact that your query is incorrect. – Shadow Apr 18 '17 at 05:12

0 Answers0