Laravel Join On Auth Table

Question

I'm using the default Authentication system that Laravel provides. However, I want to join two tables (perform two inner joins) on my Auth table (users). So far, I have created two relationships between two models and this works fine and gets me the data I want, however it performs two extra queries when I think in hindsight doing two joins on the table would be much more efficient.

What I don't know how to do is to perform the Joins on the User Model so that I'm not having to create a new query (but rather use the one that is created by calling Auth::user()).

My tables:

    Users: id username iconid avatarid

    Icons: id image

    Avatars: id image

So what I'd like to do is:

    SELECT icons.image as icon_image, avatars.image as avatar_image FROM users
    LEFT OUTER JOIN icons ON icons.id = users.iconid
    LEFT OUTER JOIN avatars ON avatars.id = users.avatarid

Is this possible? Do I just need to overwrite a method in my User model in order for Auth::user() to run the above query?

Thank you to anyone who replies!

Rupert Rupert · Accepted Answer · 2015-01-12T15:35:53

Put this in your user model:

public function icon()
{
    return $this->hasOne('Icon');
}
public function avatar()
{
    return $this->hasOne('Avatar');
}

Then get the user like this:

$user = User::with(['icon', 'avatar'])->find(1);

Then you can access the icon and avatar like this:

$user->icon

$user->avatar

To do this with joins you can do this:

$user_id = 1;
DB::table('users')
->join('icons', 'icons.id', '=', 'users.icon_id')
->join('avatars', 'avatars.id', '=', 'users.avatar_id')
->select('users.name', 'icons.path', 'avatars.path')
->where('users.id', $user_id)
->first();

Laravel Join On Auth Table

1 Answers