select coordinates at other tables

Question

I want to get all nearest items, the coordinates are saved at listings table. items and listings are many to many relationship. Then it will be sorted either by distance or low to high price.

 $items = Item::with('listings')
               ->select('*');
if($request->sortby == 'distance'){
        $items->distance($lat,$lng);
      }
if($request->sortby == 'low'){
        $items->groupBy('listings.id')
              ->orderBy('items.price');
      }
$items = $items->paginate(10);

This is my Item model for distance

public function scopeDistance($query, $lat, $lng) {
        return $query->addSelect(DB::Raw('(3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( listings.latitude ) ) * cos( radians( listings.longitude ) - radians(' . $lng . ') ) + sin( radians(' . $lat .') ) * sin( radians(listings.latitude) ) ) ) AS distance'))
                  ->orderBy('distance');
    }

this requires listings.latitude and listings.longitude which are saved in listings table. The error is

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'listings.latitude' in 'field list'

and if sorted low to high price

Column not found: 1054 Unknown column 'listings.id' in 'group statement'

how to access/select coordinates and listings for these two sorts?

Answer 1

Your Items is collection of Item not a model, thus you cannot execute distance method on this object. You have to use foreach and find distance between user position and each of the Items.

foreach($items as $item) {   
    $distance=$item->distance($userPosition);   
}

Now in the distance method you will calculate distance between user position and Item.

Secondly change $items->groupBy('listings.id') to be $items->groupBy('listing_id') where listing_id is field on items table

Answer 2

You may use join instead of eager loading here.

$items = Item::join('listings','listings.item_id','=','items.id')
               ->select('*');

Then use your logic here.

if($request->sortby == 'distance'){
        $items->distance($lat,$lng);
      }
if($request->sortby == 'low'){
        $items->groupBy('listings.id')
              ->orderBy('items.price');
      }
$items = $items->paginate(10);