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

Question

I am trying to get two related objects in Laravel using eager loading as per documentation.

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

My models are:

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 be able to access both the LeadSession and Lead objects. Here is the php code I am trying:

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

print($lead->session->id);

I have 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 get two queries executed, one for the leads table, and another for the lead_sessions table.

Is such a thing possible in Eloquent? In my view it should be a standard ORM operation, but for some reason I am struggling a whole day with it.

I don't want to use the Query Builder because I want to use the Eloquent objects and their functions afterwards.

I am coming from Python and Django and I want to replicate the behavior of select_related function in Django.

Answer 1

Try this and see if it makes more than one query

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

I hope it only runs a single query. I didnt test this. Not sure if you have to add a select() to pick the columns. But yeah, try this first.

Updates Just adding how to use both session and lead data. Try a select and specify the data you need. The reason being that if both tables have similar columns like 'id', one of them will be overwritten. So you have to alias your select like

$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 belongs to $session variable. For testing you were doing

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