بهره وری دیتابیس پروژه لاراولی - دریافت یک رکورد از رابطه has-many

در قسمت قبلی از مجموعه مقالات بهره وری دیتابیس پروژه های لاراول، شیوه کاهش مصرف مموری را مشاهده کردیم. در این قسمت خواهیم دید که چگونه می توان تنها یک رکورد از رابطه ی has-many را به کارآمد ترین روش ممکن، دریافت کرد.

طرح یک چالش

 تصور کنید در برنامه تان صفحه ای مانند زیر دارید که لیستی از کاربران، شامل نام و آدرس ایمیل شان نمایش می دهد.

درخواستی به شما می دهند که یک ستون به این صفحه اضافه کنید تا تاریخ آخرین ورود کاربر به برنامه را هم نشان دهد. این درخواست به ظاهر ساده در حقیقت پیچیدگی جالبی را با خود می آورد. نگاهی به کد می اندازیم. 

در این برنامه، ورود کاربران را در جدولی جدا ثبت می کنیم که migrations آن به صورت زیر است.

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('logins', function (Blueprint $table) {
        $table->id();
        $table->foreignId('user_id')->constrained('users');
        $table->string('ip_address', 50);
        $table->timestamp('created_at');
    });
}

هر رکورد از جدول logins شامل id کاربر، آدرس ip و زمانی است که وارد برنامه شده اند.

اگر به مدل User نگاه کنیم، رابطه ی آن با login را می توانیم ببینیم.

public function logins()
{
    return $this->hasMany(Login::class);
}

کار را با اضافه کردن ستون last login  به فایل users.balde آغاز می کنیم.

<thead>
  <tr>
    <th class="px-6 py-3 border-b border-gray-200 bg-gray-50 text-left text-xs leading-4 font-medium text-gray-500 uppercase tracking-wider">
      Name
    </th>
    <th class="px-6 py-3 border-b border-gray-200 bg-gray-50 text-left text-xs leading-4 font-medium text-gray-500 uppercase tracking-wider">
      Email
    </th>
    <th class="px-6 py-3 border-b border-gray-200 bg-gray-50 text-left text-xs leading-4 font-medium text-gray-500 uppercase tracking-wider">
      Last Login
    </th>
    <th class="px-6 py-3 border-b border-gray-200 bg-gray-50"></th>
  </tr>
</thead>

اکنون باید مقدار آخرین ورود را نمایش دهیم. اما چگونه این کار را انجام دهیم؟

بررسی راه حل ها

یک راه حل این است که برای هر کاربر یک کوئری بزنیم تا زمان آخرین ورود او را به دست آوریم.

<tbody>
  @foreach ($users as $user)
    <tr class="bg-white">
      <td class="px-6 py-4 whitespace-no-wrap border-b border-gray-200 text-sm leading-5 font-medium text-gray-900">
        {{ $user->name }}
      </td>
      <td class="px-6 py-4 whitespace-no-wrap border-b border-gray-200 text-sm leading-5 text-gray-500">
        {{ $user->email }}
      </td>
      <td class="px-6 py-4 whitespace-no-wrap border-b border-gray-200 text-sm leading-5 text-gray-500">
        {{ $user->logins()->latest()->first()->created_at->diffForHumans()}}
      </td>
      <td class="px-6 py-4 whitespace-no-wrap text-right border-b border-gray-200 text-sm leading-5 font-medium">
        <a href="#" class="text-indigo-600 hover:text-indigo-900 focus:outline-none focus:underline">Edit</a>
      </td>
    </tr>
  @endforeach
</tbody>

در کد بالا برای هر کاربر رابطه ی logins را صدا زدیم سپس با کمک متد latest ورود های کاربر را بر اساس ستون created_at مرتب کردیم. سپس با صدا زدن first یک رکورد را دریافت کردیم. اکنون به صفحه ی کاربر ها می رویم تا نتیجه را ببینیم.

 اگر نگاهی به سربرگ Queries در نوار debug بیندازیم، متوجه مشکل کوچکی می شویم. 17 کوئری برای این صفحه اجرا می کنیم که 15 تا از آن ها آنها دقیقا شبیه یک دیگر اند. برای هر کاربری که نمایش می دهیم یک کوئری می زنیم تا آخرین ورود آن ها را دریافت کنیم، یک کوئری برای دریافت لیست کاربران و یک کوئری نیز برای محاسبه تعداد کل کاربران که برای صفحه بندی داده ها مورد نیاز است! در واقع ما یک مشکل n+1 ایجاد کرده ایم. به عبارت دیگر اگر صفحه ی ما 50 کاربر را نشان می داد، ما 52 کوئری اجرا می کردیم. راه حل دیگری را امتحان می کنیم تا ببینیم می توانیم بهتر عمل کنیم یا خیر؟

این بار ورود های کاربر را eagerload می کنیم. به UserController می رویم و eagerload را به کوئری موجود اضافه می کنیم.

public function index()
{
    $users = User::query()
        ->with('logins')
        ->orderBy('name')
        ->paginate();

    return view('users', ['users' => $users]);
}

حال فایل users.blade را ویرایش می کنیم.

<td class="px-6 py-4 whitespace-no-wrap border-b border-gray-200 text-sm leading-5 text-gray-500">
  {{ $user->logins->sortByDesc('created_at')->first()->created_at->diffForHumans()}}
</td>

از آن جایی که رابطه ی logins را eagerload کرده ایم، collection ای از ورود های کاربر داریم. از متد sortByDesc در collection ها استفاده می کنیم تا ورودها را مرتب کرده و سپس با متد first، آخرین ورود را دریافت می کنیم.

در حالت قبلی 17 کوئری اجرا می شد اما در این حالت تنها 3 کوئری به دیتابیس زده می شود. به این ترتیب ما مشکل n+1 را از بین بردیم. اما با این کار یک مشکل بزرگ تر ایجاد کردیم. اگر نگاهی به سربرگ models بیندازیم متوجه می شویم که تعداد بسیار زیادی مدل نسبت به حالت قبلی ایجاد شده است. در seeder این برنامه، ما برای هر کاربر 500 ورود ذخیره کردیم. به همین دلیل برای 15 کاربری که در این صفحه نمایش می دهیم  7500 مدل Login فراخوانی شده است. همچنین اگر به مصرف مموری نگاه کنیم متوجه می شویم حدود 8 مگابایت از حالت قبلی بیشتر است.

این تنها یک مثال ساده از این مشکل معمول است. ممکن است  شما به راحتی در برنامه های خود میلیون ها رکورد را از دیتابیس بخوانید و مصرف مموری و cpu سرور خود را به شدت افزایش دهید.

این مثال از نظر من به این دلیل جالب است که معمولا هنگامی که به مشکل n+1 بر می خوریم از eagerload برای حل مشکل استفاده می کنیم اما در این شرایط eagerload کردن نتیجه را بدتر می کند.

در چنین شرایطی ممکن است فکر کنید تنها راه خوب برای حل این مشکل این است که ستونی به جدول کاربر ها اضافه کنید و آخرین ورود کاربر را در آن نگه دارید. برای انجام چنین کاری باید به migrations مربوط به کاربرها برویم و ستونی مانند last_login_id را به آن اضافه کنیم.

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('email')->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->foreignId('last_login_id')->constrained('logins');
        $table->timestamps();
    });
}

با داشتن چنین ستونی، هر بار که کاربر وارد برنامه می شود یک رکورد در جدول logins برای آن دخیره می کنیم و ستون last_login_id را با شناسه ی آن به روزرسانی می کنیم. برای نمایش آخرین ورود هم به راحتی رابطه ی مربوط به last_login_id را eagerload می کنیم. این راه حل کاملا صحیح و کارآمد است. به این ترفند denormalization گفته می شود. البته بسیاری از اوقات اجرای این ترفند اینقدر ساده نیست و می تواند به سرعت پیچیده شود. پس قبل از اینکه این راه حل را انتخاب کنیم روشی که من به طور معمول برای حل این گونه مشکلات استفاده می کنم را باهم بررسی می کنیم. این روش استفاده از sub-query است.

Sub-query ها به ما اجازه می دهند تا ستونی را به نتیجه ی کوئری خود اضافه کنیم که از جدول دیگری محاسبه می شود. بنابراین ما می توانیم یک sub-query در کوئری کاربر ها اضافه کنیم تا تاریخ آخرین ورود را برای ما برگرداند.

برای انجام این کار به UserController می رویم و eagerload را حذف می کنیم و به جای آن یک sub-query اضافه می کنیم. لاراول از نسخه ی 6 به بعد متدی برای اضافه کردن sub-query قرار داده است. این کار را با استفاده از متد addSelect انجام می دهیم.

public function index()
{
    $users = User::query()
        ->addSelect([
            'last_login_at' => Login::query()
                ->select('created_at')
                ->whereColumn('user_id', 'users.id')
                ->latest()
                ->take(1)
        ])
        ->orderBy('name')
        ->paginate();

    return view('users', ['users' => $users]);
}

یک آرایه، به متد addSelect می دهیم که کلید آن نام ستونی است که می خواهیم در نتیجه کوئری به ما برگردد. نام آن را last_login_at می گذاریم. مقدار این کلید همان کوئری ای است که می خواهیم به عنوان sub-query اجرا کنیم. از مدل Login استفاده می کنیم تا ستون created_at را انتخاب کنیم و سپس شرط sub-query مان را می نویسیم. در این شرط قید می کنیم که مقدار user_id در جدول logins برابر با مقدار id در جدول users باشد. سپس ورود ها را مرتب می کنیم و با استفاده از متد take تنها یک نتیجه را می گیریم. زیرا در sub-query ها تنها باید یک نتیجه بر گردانده شود.

اکنون فایل users.blade مان را ویرایش می کنیم تا مقدار last_login_at را نشان دهد.

<tbody>
  @foreach ($users as $user)
    <tr class="bg-white">
      <td class="px-6 py-4 whitespace-no-wrap border-b border-gray-200 text-sm leading-5 font-medium text-gray-900">
        {{ $user->name }}
      </td>
      <td class="px-6 py-4 whitespace-no-wrap border-b border-gray-200 text-sm leading-5 text-gray-500">
        {{ $user->email }}
      </td>
      <td class="px-6 py-4 whitespace-no-wrap border-b border-gray-200 text-sm leading-5 text-gray-500">
        {{ $user->last_login_at}}
      </td>
      <td class="px-6 py-4 whitespace-no-wrap text-right border-b border-gray-200 text-sm leading-5 font-medium">
        <a href="#" class="text-indigo-600 hover:text-indigo-900 focus:outline-none focus:underline">Edit</a>
      </td>
    </tr>
  @endforeach
</tbody>

توجه داشته باشید که دیگر نمی توانیم از تابع diffForHumans استفاده کنیم. زیرا دیگر کلاس carbon برای تاریخ خوانده شده از دیتابیس در دسترس ما نیست. این مورد را جلوتر اصلاح می کنیم. اکنون سری به صفحه ی کاربر ها می زنیم تا تغییر ها را مشاهده کنیم.

همانطور که مشاهده می کنید تاریخ آخرین ورود به درستی نشان داده می شود (اگر چه فرمت مناسبی ندارد) و مصرف مموری ما به حالت عادی برگشته است. همچنین تنها 15 مدل فراخوانی شده است. در سربرگ Queries هم تنها دو کوئری اجرا شده اند. این یک بهبود بسیار بزرگ محسوب می شود که با استفاده از sub-query ممکن شد. اکنون نگاه دقیق تری به کوئری ای که اجرا شده است می اندازیم.

اگر این کوئری را اجرا کنیم نتیجه ای مانند زیر دریافت می شود.

همانطور که می بینید ستون last_login_at در نتایج دیده می شود. در صورتی که این ستون در جدول users وجود ندارد و هنگام اجرای کوئری ساخته و محاسبه شده است.

sub-query نوشته شده را بین دو پرانتز مشخص شده می توانید ببینید.

SELECT
`users`.*,
(
SELECT
`created_at`
FROM
`logins`
WHERE
`user_id` = `users`.`id`
ORDER BY
`created_at`
DESC
LIMIT 1
) AS `last_login_at`
FROM
`users`
ORDER BY
`name` ASC
LIMIT 15 OFFSET 0

از جدول logins ستون created_at را انتخاب کردیم به شرطی که ستون user_id آن با ستون id در کوئری اصلی برابر باشد و پس از آن نتیجه را بر اساس created_at به صورت نزولی (desc) مرتب کردیم و در انتها از limit 1 استفاده کردیم تا تنها یک نتیجه برگردد. پس از این، نتیجه ی sub-query را به ستونی با نام last_login_at اختصاص داده ایم.

اکنون شاید این سوال برای شما به وجود آمده باشد که با انجام این کار، آیا مشکل کوئری های متعدد در لاراول را به لایه دیتابیس منتقل نکردیم؟ جواب آن هم آری و هم خیر است. بله، اکنون دیتابیس ما وظیفه اجرای همه این کوئری ها را دارد. یک کوئری برای گرفتن کاربر و یک sub-queryبه ازای هر کاربر جهت دریافت آخرین ورود.

 اما دیتابیس ها برای انجام کارهایی از این دست، به شدت بهینه سازی شده اند و گزینه ی مناسب تری نسبت به لاراول و PHP هستند. علاوه بر این، از دیدگاه لاراول، ما تنها یک کوئری برای دریافت نتیجه اجرا می کنیم که باعث می شود رفت و آمد کمتری بین برنامه ی ما و دیتابیس وجود داشته باشد. این مورد باعث می شود نتیجه ی نهایی کارایی بهتری داشته باشد.

اکنون به فایل users.blade بر می گردیم تا تابع diffForHumans را اضافه کنیم.

<td class="px-6 py-4 whitespace-no-wrap border-b border-gray-200 text-sm leading-5 text-gray-500">
    {{ $user->last_login_at->diffForHumans()}}
</td>

اکنون پس از مشاهده ی صفحه کاربرها با خطای زیر مواجه می شویم.

این خطا به ما اطلاع می دهد که متد diffForHumans که در کلاس carbon موجود است را، روی یک رشته صدا زده ایم که کاملا هم صحیح است. قبل از اینکه از sub-query استفاده کنیم، چنین مشکلی نداشتیم زیرا مدل Login ستون created_at را به صورت خودکار به یک شیء از کلاس carbon تبدیل می کرد. اکنون که این مقدار را مستقیم از دیتابیس می خوانیم، این تبدیل اتفاق نمی افتد.

خوشبختانه لاراول برای چنین مشکلی راه حل دارد. در UserController از متد withCasts استفاده می کنیم تا ستون مورد نظر را به یک شیء از carbon تبدیل کنیم.

public function index()
{
    $users = User::query()
        ->addSelect([
            'last_login_at' => Login::query()
                ->select('created_at')
                ->whereColumn('user_id', 'users.id')
                ->latest()
                ->take(1)
        ])
        ->withCasts(['last_login_at' => 'datetime'])
        ->orderBy('name')
        ->paginate();

    return view('users', ['users' => $users]);
}

با انجام این کار مشکل رفع می شود.

قبل از اینکه این مطلب را به پایان برسانیم، بیاید sub-query نوشته شده را به یک scope در مدل User تبدیل کنیم. ابتدا با بریدن sub-query از UserController شروع می کنیم.

public function index()
{
    $users = User::query()
       ->withLastLoginAt()
        ->orderBy('name')
        ->paginate();

    return view('users', ['users' => $users]);
}

sub-query ای که داشتیم را با متد withLastLoginAt جایگزین کردیم. حال به مدل User می رویم و scope مورد نظرمان را می سازیم.

public function scopeWithLastLoginAt($query)
{
    $query->addSelect([
        'last_login_at' => Login::query()
            ->select('created_at')
            ->whereColumn('user_id', 'users.id')
            ->latest()
            ->take(1)
    ])
        ->withCasts(['last_login_at' => 'datetime']);
}

اکنون اگر صفحه کاربرها را refresh کنید مشاهده خواهید کرد که همه چیز مانند قبل درست کار می کند. از نظر من بردن کوئری ها به scope داخل مدل، خوانایی کد کنترلرها را افزایش می دهد و این امکان را در اختیار ما قرار می دهد که جای دیگر هم از این کوئری استفاده کنیم. 

در قسمت بعدی، این ترفند را به یک رابطه dynamic تبدیل خواهیم کرد.