How to Eager loading other model from DB::Select() Query?

Hi there, I have a query that grabs the 60 closest locations near a given longitiude latitude point. What I want to do is eager load my another model on top of this. Any ideas on if this is possible or another work around?

    $circleRadius = 3959;
    $maxdistance = 60;
    
    // SQL statement that finds closest 60 locations within radius of 60 miles to $lat $lng.
    $locations = DB::select('SELECT id, name, longitude, latitude,( ' . $circleRadius . ' * acos( cos( radians(' . $lat . ') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(' . $lng . ') )+ sin( radians(' . $lat . ') ) * sin( radians( latitude ) ) ) ) 
    AS distance 
    FROM businesses 
    HAVING distance < ' . $maxdistance . ' 
    ORDER BY distance 
    LIMIT 60');

Adding ->with('ratings')->get(); to the end of the query does not work

jlrdw

8 years ago

Level 75

Is ratings linked as a child table to businesses?

If so you write a second query using (business_id) or whatever field you linked.

See https://drive.google.com/file/d/0B1_PFw--3o74RDFscjluVlBpNWc/view

Here I have a P.O. I want to edit, so in the edit page there is one query to bring up the main P.O. and a second query to pull in the sub-items on that P.O.

Not perfect (columns not aligned), it was a quick example I did a while back.

The other way would be use the ORM, but not necessary as orm still converts to regular sql at runtime.

jpeterson579

8 years ago

Level 7

@jlrdw But wont that produce a ton of queries? Like if I have 60 results for the 1st query, then i need to run 1 seperate query for each. So thats 61 total queries...

Hence why I wanted to eager load it all.

Am I missing something here?

jlrdw

8 years ago

Level 75

No one query See last answer here for querybuilder example http://laravel.io/forum/05-12-2015-has-many-through-relationship-depth

Actually 2 one parent query, one child query.

Edit: Is this setup like a one to many? I don't know your tables.

Orm eager loading does same, queries the data that matches an id, like show me all kids that Billy Bob has. One query for Billy Bob, one query eager loaded for his kids.

But it seems you are not using orm.

jpeterson579

8 years ago

Level 7

@jlrdw

Business Model

// A business has many ratings
public function ratings()
{
    return $this->hasMany(Rating::class);
}

Ratings Model

// A rating belong to a business
    public function business()
{
    return $this->belongsTo(Business::class);
}

jlrdw

8 years ago

Level 75

But you are using

DB::select--

If you use orm you need the ->with part

businesses with locations.

Doc example

$comments = App\Post::find(1)->comments;

foreach ($comments as $comment) {
    //
}

Saying find the comments with a linked post_id

Please or to participate in this conversation.