laravel - 用 Eloquent 快速加载

Question

我有以下代码:

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

和:

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

现在使用 laravel-debugbar，当我打开我的用户页面时，我看到正在执行 107 个查询使得页面加载非常缓慢，我有一种感觉，当我添加更多用户时，执行的查询总数将会增加.我已经查看了代码，现在我无法判断它有什么问题或如何减少生成的查询数量。

这些是我的模型:

用户.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();
    }
}

角色.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');
    }
}

这似乎是重复的查询:

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'

编辑:
问题似乎来自我的角色，我正在查看 Controller 和模型中的代码，但我不知道哪里出了问题。我改了 $roles = Role::withCount('users')->get();至 $roles = Role::with('users')->withCount('users')->get();现在我生成的查询更少了(71 个仍然很多)。

Answer 1

正是用户模型上的 appends 角色属性导致了如此多的过度查询。作为测试注释掉附加角色并测试然后运行多少查询。您可能需要延迟加载角色以最大限度地减少查询数量。