CODEWITHSUNDEEP
laraveleloquentlumen
mertje
mertje

Reputation: 486

Eloquent sort by relationship column

I'm kind of lost, i'm trying to count the distance and sort mission closest to the logged in user. The problem is that my lat and lng is in another table (Buildings), which is defined as a relation in Mission. I'm using Laravel/Lumen's QueryBuilder to fetch records from the database. 

$orderBy = "DEGREES(ACOS(COS(RADIANS($lat)) * COS(RADIANS(lat)) * COS(RADIANS($lng) - RADIANS(lng)) + SIN(RADIANS($lat)) * SIN(RADIANS(lat))))";
return DB::table('buildings')
       ->join('missions', 'building_id', '=', 'buildings.id')
       ->select('missions.*', 'buildings.lat', 'buildings.lng')
       ->orderBy(DB::raw($orderBy))
       ->get();

result:

{
  "id": 5,
  "building_id" : 2
   ...
  "created_at": "2016-03-07 07:35:19",
  "updated_at": "2016-03-07 07:35:19",
  "lat": 33,
  "lng": 55
}

But this returns quite ugly response and hard to work with. Since Mission have relation with a Building, I would like to load that table to the response too (Instead of displaying building_id). But with DB:Table() it's not possible(?)

{
  "id": 5,
  "building_id": 2
  "building" : {
      "id": 2
      ...
      "lat": 33,
      "lng": 55
  }
  ...
}

This is the response I would like to get, and be sorted by lat/lng. Is there any possible way to do this with Eloquents models?

Upvotes: 1

Views: 995

Answers (1)

krisanalfa
krisanalfa

Reputation: 6428

Does this work to you?

$orderBy = "DEGREES(ACOS(COS(RADIANS($lat)) * COS(RADIANS(lat)) * COS(RADIANS($lng) - RADIANS(lng)) + SIN(RADIANS($lat)) * SIN(RADIANS(lat))))";

return Building::join('missions', 'building_id', '=', 'buildings.id')
    ->select(['missions.*', 'buildings.lat', 'buildings.lng'])
    ->with('missions')
    ->orderBy(DB::raw($orderBy))
    ->get();

PS: Don't forget to set the relation in your Building model, unless the with method will fail.

Upvotes: 2

Related Questions