How to get two related objects in Laravel (Eloquent) with one SQL query

Question

I am trying to get two related objects in Laravel using the download as per the documentation.

https://laravel.com/docs/5.4/eloquent-relationships#eager-loading

My models:

class Lead extends Model { public function session() { return $this->hasOne('App\LeadSession'); } } class LeadSession extends Model { public function lead() { return $this->belongsTo('App\Lead'); } } 

I want to get both objects with one SQL query. Basically I want to execute:

 select * from lead_sessions as s inner join lead as l on l.id = s.lead_id where s.token = '$token'; 

and then have access to the LeadSession and Lead objects. Here is the php code I'm trying to do:

 $lead = Lead::with(['session' => function ($q) use ($token) { $q->where('token','=',$token); }])->firstOrFail(); print($lead->session->id); 

I also tried:

 $lead = Lead::whereHas('session', function($q) use ($token) { $q->where('token','=',$token); })->firstOrFail(); print($lead->session->id); 

and

 $session = LeadSession::with('lead')->where('token',$token)->firstOrFail(); print($session->lead->id); 

In all three cases, I run two queries: one for the leads table, and the other for the lead_sessions table.

Is this possible in Oratory? In my opinion, this should be a standard ORM operation, but for some reason I have been struggling with it all day.

I do not want to use Query Builder, because after that I want to use Eloquent objects and their functions.

I come from Python and Django, and I want to replicate the behavior of the select_related function in Django.

Answer 1

Try this and see if it makes more than one request

 $session = LeadSession::join('leads', 'leads.id', '=', 'lead_sessions.lead_id') ->where('token',$token) ->firstOrFail(); 

Hope it only runs one request. I have not tested this. Not sure if you need to add select() to select columns. But yes, try it first.

Update Just by adding how to use session and lead data. Try to select and specify the necessary data. The reason is that if both tables have similar columns, such as "id", one of them will be overwritten. Thus, you must indicate your choice, for example

 $session = LeadSession::join('leads', 'leads.id', '=', 'lead_sessions.lead_id') ->where('token',$token) ->select( 'lead_sessions.*', 'leads.id as lead_id', 'leads.name', 'leads.more_stuff' ) ->firstOrFail(); 

Now all this data relates to the $session variable. For testing you did

 print($lead->session->id); //becomes print($session->lead_id); //we aliased this in the query