Eager Loading with Eloquent

Question

I have the following code:

$users = User::with('profile')->paginate($count);

and:

$roles = Role::withCount('users')->get();

Now using laravel-debugbar and when I open my users page what I see is that 107 queries are being executed making the page load really slowly and I have a feeling that when I add more users the number total number of queries executed is going to increase. I have looked at the code and right now I can't tell what is wrong with it or how to reduce the number of queries being generated.

These are my models:

User.php

<?php

namespace App\Models;

use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Kodeine\Acl\Traits\HasRole;

class User extends Authenticatable
{
    use HasRole, Notifiable;
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'email', 'password',
    ];

   protected $table = "users";
    protected $appends = ['role'];

   public function profile()
    {
        return $this->hasOne('App\Models\Profile');
    }

   public function getRoleAttribute() {
        return $this->getRoles();
    }
}

Role.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Kodeine\Acl\Traits\HasPermission;
use Cviebrock\EloquentSluggable\Sluggable;

class Role extends Model
{
    use HasPermission, Sluggable;

   protected $table = "roles";

   /**
     * The attributes that are fillable via mass assignment.
     *
     * @var array
     */
    protected $fillable = ['name', 'slug', 'description'];

   /**
     * Roles can belong to many users.
     *
     * @return Model
     */
    public function users()
    {
        return $this->belongsToMany('App\Models\User', 'role_user', 'role_id', 'user_id');
    }

   /**
     * List all permissions
     *
     * @return mixed
     */
    public function getPermissions()
    {
        return $this->getPermissionsInherited();
    }

   /**
     * Checks if the role has the given permission.
     *
     * @param string $permission
     * @param string $operator
     * @param array  $mergePermissions
     * @return bool
     */
    public function can($permission, $operator = null, $mergePermissions = [])
    {
        $operator = is_null($operator) ? $this->parseOperator($permission) : $operator;

       $permission = $this->hasDelimiterToArray($permission);
        $permissions = $this->getPermissions() + $mergePermissions;

       // make permissions to dot notation.
        // create.user, delete.admin etc.
        $permissions = $this->toDotPermissions($permissions);

       // validate permissions array
        if ( is_array($permission) ) {

           if ( ! in_array($operator, ['and', 'or']) ) {
                $e = 'Invalid operator, available operators are "and", "or".';
                throw new \InvalidArgumentException($e);
            }

           $call = 'canWith' . ucwords($operator);

           return $this->$call($permission, $permissions);
        }

       // validate single permission
        return isset($permissions[$permission]) && $permissions[$permission] == true;
    }

   /**
     * @param $permission
     * @param $permissions
     * @return bool
     */
    protected function canWithAnd($permission, $permissions)
    {
        foreach ($permission as $check) {
            if ( ! in_array($check, $permissions) || ! isset($permissions[$check]) || $permissions[$check] != true ) {
                return false;
            }
        }

       return true;
    }

   /**
     * @param $permission
     * @param $permissions
     * @return bool
     */
    protected function canWithOr($permission, $permissions)
    {
        foreach ($permission as $check) {
            if ( in_array($check, $permissions) && isset($permissions[$check]) && $permissions[$check] == true ) {
                return true;
            }
        }

       return false;
    }


   /**
     * Return the sluggable configuration array for this model.
     *
     * @return array
     */
    public function sluggable()
    {
        return [
            'slug' => [
                'source' => 'name'
            ]
        ];
    }
}

Profile.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Profile extends Model
{
    protected $table = "profiles";

   public function user()
    {
        return $this->belongsTo('App\Models\User');
    }
}

This seems to be the query being repeated:

select `roles`.*, `role_user`.`user_id` as `pivot_user_id`, `role_user`.`role_id` as `pivot_role_id`, `role_user`.`created_at` as `pivot_created_at`, `role_user`.`updated_at` as `pivot_updated_at` from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `role_user`.`user_id` = '4'

Edit: The issue seems to be coming from my roles, I'm looking at the code in my controller and in my model and I can't tell what is wrong. I changed $roles = Role::withCount('users')->get(); to $roles = Role::with('users')->withCount('users')->get(); and now I have fewer queries generated (71 still a lot).

Answer 1

It's the appends role attribute on the user model that is causing so many excessive queries. As a test comment out appends roles and test how many queries run then. You'll likely need to lazy load roles to keep the number of queries minimized.