Eager loading, joins in Laravel

Using with and joins all together

Special thanks to Dani Mora (@moraDmp30) for his advices and help everyday at work. Nice to work with this superb team.

Eager loading

You are probably used to this concept. But just a small introduction.
Any modern application uses nowadays the MVC paradigm. We have Model, Views and Controllers. ORM (Object-Relational Mapping) is a must. An ORM (Eloquent in Laravel ecosystem) is very handy and just maps tables into objects, it basically is a sub-layer between the DB and the app so that you use objects to insert, update, deleted or get data from your tables.
Models have typically relationships to other model such as "hasMany", "hasOne" or "belongsTo" (this is not a tutorial about this so just look for more info if you need some help).
In Laravel you can do thing like this:
$post = Post::findOrFail(1);
$comments = $post->comments;

This is call "lazy loading", the ORM will query the DB in the first sentence to select the post which ID is 1 and then it will query the DB again to get the comment where 'post_id' (assuming this is the foreign key) is 1 (the post->id). That works but now imagine this situation:
$posts = Post::where('published', 1)->get();
foreach ($posts as $post) {
$comments = $post->comments;

Potentially the chuck above will perform 1 query to get N posts published + N query to get the specific comment for each of them. No way. Lazy loading + loops just will get down your server by hammering the DB.

Eager loading to the rescue!
$posts = Post::where('published', 1)->with('comments')->get();
foreach ($posts as $post) {
$comments = $post->comments;

Beautiful. By using 'with' you say Laravel to "preload" all these relations so that the ORM will not have to query each time. Behind the scenes Laravel does some black magic. Like this:

  1. Do a query to get the post. Imagine we get 3 post (1, 3, 17)

  2. Do a second query to get any comment where post_id in (1, 3, 17)

  3. Set the relationship properties in each $post using the results from the second query

All these stuff happens magically which is pretty nice but PAY ATTENTION:

  'With' will work weirdly bad if you use joins

What? Why?

$posts = Post::where('published', 1)->with('comments')->join('users', 'users.id', '=', 'posts.user_id')->get();
foreach ($posts as $post) {
$comments = $post->comments;

Ok, imagine for some reason you have to do a join. It should works BUT the problem is behind the scenes Eloquent will use the last "id" field within the results in the query. In that case we have posts.id and users.id in the result and 'with' will try to get any comment where id in (users IDs).

I'm assuming Laravel has a elegant way to solve it (but I don't know what it is). The only thing we should do as a workaround is adding "->select(posts.*)" to the main query so that the users.id will not bother us or use an alias for users.id in if you need it.
 Let me know if you have any question. Hope it helps.