Apr 25, 2018

Level 2

Optimize eloquent query

I'm facing a performance issue when fetching my user models.

My user model has a relation with many teams. The user has a separate profile for each team.

These are my db tables:

users

id
first_name
last_name

profiles

id
role
position
nickname
back_number

teams

id
name

team_user (pivot)

team_id
user_id
profile_id

I'm looking for a better approach to query my users with desired profile for a specific team with the possibility to filter by profile role.

My models:

User.php

class User extends Model
{
    public function teams()
    {
        return $this->belongsToMany(Team::class)->using(TeamUser::class)->withPivot('profile_id');
    }

    public function scopeFilter($query, $filters=null)
    {
        if (isset($filters['type'])) {
            $query->where('profiles.type_id', $filters['type']);
        } else {
            $query->where('profiles.type_id', '!=', 3);
        }
    }

    public function scopeOwners($query)
    {
        return $query->where('profiles.role', 2);
    }

    public function scopeWebmasters($query)
    {
        return $query->where('profiles.role', '>', 0);
    }

    public function scopeTeamProfile($query, $teamId)
    {
        return $query
            ->select('users.*')
            ->join('team_user', 'team_user.user_id', '=', 'users.id')
            ->join('profiles', 'profiles.id', '=', 'team_user.profile_id')
            ->where('team_user.team_id', $teamId)
            ->whereNull('profiles.deleted_at')
            ->orderBy('profiles.type_id')
            ->orderBy('users.last_name');
    }

    public function getProfileAttribute()
    {
        $teamId = 1;

        $team = $this->teams->firstWhere('id', $teamId);
        $profile = $team->pivot->profile;

        return $profile;
    }
}

TeamUser.php

class TeamUser extends Pivot
{
    public function profile()
    {
        return $this->belongsTo('App\Profile');
    }
}

Inside my controller

$team = 1;
$users = User::filter(['type' => $requestType])
    ->teamProfile($team)
    ->paginate(25);

But now the main issue. When access the profile data within the loop in blade:

{{ $user->profile->position }}
{{ $user->profile->nickname }}
{{ $user->profile->back_number }}

This results in a lot of extra unnecessary queries. I suppose the methods getProfileAttribute() and scopeTeamProfile() are the problem.

Any help would be great!

Regards, Bas

hotgeart

8 years ago

Level 1

Don't use "select *", select the fields you need : "select users.name, ..."
"profiles" table is really necessary? You could putt that in users if its only 4 fields.
Add index

basvandertogt

8 years ago

Level 2

The select * is not a big performance problem for now. The profiles table is necessary because it's related to the team. I will add a index. Tnx but i still have a big performance problem because getProfileAttribute() is called multiple times within blade (25 results * 3 = 75).

fridzema

8 years ago

Level 2

I think you are looking for this https://laravel.com/docs/5.6/eloquent-relationships#eager-loading

In your case something like

    User::with('profile')
    ->filter(['type' => $requestType])
    ->teamProfile($team)
    ->paginate(25);

And as replied before don't use select *, go for something like this

    User::select('id', 'profile.position', 'profile.nickname', 'profile.back_number')
    with('profile')
    ->filter(['type' => $requestType])
    ->teamProfile($team)
    ->paginate(25);

basvandertogt

8 years ago

Level 2

@fridzema I think so too, but profile is beyond the pivot table (team_user). with('profile') does not work.

Vilfago

8 years ago

Best Answer

Level 20

with('team.profile') ?

but I think you have a problem with your database as you merge 2 pivot tables.

Is the profile related to many teams, or many users ?

If I understand well : One user as many team, and for each team he has a specific profile. Thus, profile is related to one team.

User many-to-many Team
User One-to-many Profile
Team One-to-many Profile

So you can :

keep your users and teams table.
delete the profile_id column in team_user
add user_id and team_id in your profile table
update your relationships

and go with : with('team.profile')

basvandertogt

8 years ago

Level 2

@Vilfago You're right "If I understand well : One user as many team, and for each team he has a specific profile. Thus, profile is related to one team."

I thought it would not be problem to combine to tables in one pivot table. I'll try your suggestion! Tnx!

Vilfago

8 years ago

Level 20

Probably that you can make it working, but I don't know how. Just find it's cleaner the way I wrote it above.

Edit :

class User extends Model
{
    public function teams()
    {
        return $this->belongsToMany('App\Team', 'team_user');
    }
    public function profiles()
    {
        return $this->belongsToMany('App\Profile', 'team_user');
    }


class Team extends Model
{
    public function users()
    {
        return $this->belongsToMany('App\User', 'team_user');
    }
    public function profiles()
    {
        return $this->belongsToMany('App\Profile', 'team_user');
    }


class Profile extends Model
{
    public function user()
    {
        return $this->belongsToMany('App\User', 'team_user');
    }
    public function team()
    {
        return $this->belongsToMany('App\Team', 'team_user');
    }

Maybe it works, and you can use User::with('team.profile)->get() with this. But I think it's not a good way as you have to configure a many to many relationship where in fact is a one to many.

basvandertogt

8 years ago

Level 2

@Vilfago Thank you very much for your help. I changed the db structure and it's much faster now.

Please or to participate in this conversation.