Retrieve Laravel Model results based on multiple ID's and first hasMany images table

1
votes

I have implemented finding locations with mysql query to find nearest listing on my google maps using laravel 5.

$query = DB::select(DB::raw('SELECT id, ( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(' . $lng . ') ) + sin( radians(' . $lat .') ) * sin( radians(latitude) ) ) ) AS distance FROM listings HAVING distance < ' . $distance . ' ORDER BY distance') );
    $ids = [];

    foreach($query as $q)
    {
      array_push($ids, $q->id);

    }

it returns multiple ids, which then I can retrieve the listing using whereIn 

$results = Listing::whereIn('id', $ids)->paginate(15);

Now the problems is, each listing has many images on different table (images table), I want to retreive first image only then pass the results to view (list all the listings with 1 image)

What would the correct way to solve this?

Thank you!

2
phpmysqllaraveleloquenthas-many

2 Answers

0
votes

I supose that you want to get:

$listing = Listing::whereIn('id', $ids)->first();

where $listing is a first entry.

0
votes

This is called "select by group" and is a bit of a hack. MySQL breaks SQL spec in how it does this and you should be aware that if you intend to be database agnostic you'll cause yourself a lot of grief selecting by group.

So, for instance, if you had the class ListingImage:

$listings = Listing::whereIn('id', $ids)->paginate(15);
$images   = ListingImage::whereIn('listing_id', $ids)->groupBy('listing_id')->get();

(As an side, you can use $query->pluck('id') to get a collection without using that loop.)