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.

Just to make sure I understand your question, you want Eloquent to only produce 1 query instead or 2? — Rwd
– Rwd, Commented Mar 2, 2017 at 10:49
@RossWilson, yes. Both models can be retrieved using a join query — Martin Taleski
– Martin Taleski, Commented Mar 2, 2017 at 10:49

EddyTheDove · Accepted Answer · 2017-03-07 22:42:46Z

2

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

edited Mar 7, 2017 at 22:42

answered Mar 2, 2017 at 12:02

EddyTheDove

13.2k2 gold badges40 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

20 Comments

EddyTheDove Over a year ago

$session->lead->id is the one making the query through eager loading. Read here: laravel.com/docs/5.4/eloquent-relationships#eager-loading the query is made when you write that for the first time. What I was proposing was for you to get all the data in one go then use without eager loading.

Martin Taleski Over a year ago

I have removed the eager loading code, still the same. it first executes one query with a join: select * from lead_sessions inner join leads on leads.id = lead_sessions.lead_id where token = ? and then goes onto executing another one: select * from leads where leads.id = ?

Martin Taleski Over a year ago

yes, I have edited my previous comment, and added both queries that execute on the print. After the print I have a die(), so it stops.

EddyTheDove Over a year ago

I have to admit you have a pretty complexe use case. It's a good challenge, but I have to admit I can't see a way to do that in one query using Eloquent.

Martin Taleski Over a year ago

not a pretty complex use case. I just want to optimize the db operations in the application. The actual action is more complex and it joins four related models so I want to get them all in one query instead of executing four. It should be quite a common operation for an ORM.

|

Collectives™ on Stack Overflow

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

1 Answer 1

20 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

20 Comments

Your Answer

Sign up or log in

Post as a guest

Related