Eloquent eager loading acting as explicit join

Question

I am building a small blog and I would like to use the built-in Eloquent eager loading, but I would like it to act as an explicit join.

Here's what I'm trying to do, using a join, which works, but not in the way I want.

$posts = Post::join('users', function($join){
    $join->on('users.id', '=', 'posts.user_id');
    $join->on('users.status', '=', DB::raw('active'));
})
->get();

My problem with this is that I can't use the user model on my post like so:

$posts[0]->user->firstname;

With the join, the user's data is directly set on the post model, so I have to use it like so:

$posts[0]->firstname;

The thing is: I would like to use my User model because it has a few method inside that I'd like to use. One for printing the full name, one for printing its URL, etc..

What I am not able to do with eager loading, is to prevent a post from loading when it has no user attached to it. When the user associated with the post doesn't have the status 'active', I still get the post, and NULL for the user. But I don't want the post at all.

Is that something possible? Am I being clear enough in my explications?

Thanks

Look at eager load constraints. Don't worry that it doesn't do a join, it's not a big problem - the queries will end up in the query cache anyway, so in some cases it may actually be quicker than many joins. — Rich Bradshaw, Sep 21 '14 at 15:50
I looked at them, but I can't seem to find a way in which I wouldn't get the post if there is no user attached to it or if its user is inactive. — Jonathan Pellerin, Sep 21 '14 at 15:51
Just made a stupid comment, sorry - can't think of best way to do this. — Rich Bradshaw, Sep 21 '14 at 15:54

score 2 · Accepted Answer · answered Sep 21 '14 at 16:21

You're overcomplicating things. Eloquent has everything you need:

// first make sure you load only posts of active users
$posts = Post::whereHas('user', function ($q) {
  $q->where('status', 'active');
})
// then eager load the users
->with('user')
->get();

And by the way this is how you do, what you tried, in your join (w/o DB::raw):

$posts = Post::join('users', function($join){
    // on() is for comparing fields
    $join->on('users.id', '=', 'posts.user_id');

    // while where is for comparing to provided values/strings
    // just like simple query where()
    $join->where('users.status', '=', 'active');
})

Cool! Thanks! That's exactly what I wanted – Jonathan Pellerin Sep 21 '14 at 17:07 — Jonathan Pellerin, Sep 21 '14 at 17:07

Eloquent eager loading acting as explicit join

1 Answers1