Laravel クエリスコープを使おう！🐱 Query Scope

Laravel

ORM？流行りものでしょ
SQLで良くね？😨

→
クエリスコープで保守しやすくなるぞ🐱

●抽象的に内容がわかるから保守性アップ
●条件分岐と組みあわせてクエリの組み立ての柔軟性
●コードが短く目と頭にやさしい#駆け出しエンジニア #プログラミング初心者 https://t.co/5sbnGp5iYW

— 優さん🌷わくわく開発YouTuber (@yuu13n6) May 16, 2020

まずはSQLとQuery Builderの比較

中間テーブルとの結合のSQLを書いてみる

SQLで書いた場合

<?php

namespace App\Repositories\Status\Member;

use App\Http\Models\Members;
use App\Http\Models\Section;
use Illuminate\Http\Request;
use App\Repositories\Status\Member\PerformanceListRepositoryInterface;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Pagination\Paginator;

class PerformanceListRepository implements PerformanceListRepositoryInterface
{
    public function execute()
    {
       return self::getMembersWithSection();
    }

    public function getMembersWithSection()
    {
        $sql = <<< SQL
        SELECT
        tb_member.member_id,
        tb_member.fullname,
        tb_member.count_burning,
        tb_member.count_join_project,
        tb_member.count_pm,
        tb_member.count_member,
        tb_member.count_sales,
        tb_member.skill,
        tb_sectiosection_name
        FROM
        tb_member
        INNER JOIN
        (
            SELECT
            member_id,
            section_id,
            created
            FROM
            tb_member_section
            WHERE
            deleted = 0
            AND
            created = (
                SELECT
                  MAX(created)
                FROM
                  tb_member_section AS tmp_tb_member_section
                WHERE
                  tb_member_section.member_id = tmp_tb_member_section.member_id
                AND 
                  tb_member_section.deleted = 0
                )
            ) as ud
        ON tb_member.member_id = ud.member_id
        LEFT JOIN
        tb_section
        ON tb_section.section_id = ud.section_id
        AND tb_section.deleted = 0
        WHERE (tb_member.deleted = 0)
        GROUP BY ud.member_id

SQL;

    }
}

QueryBuilderで書いた場合

<?php

namespace App\Repositories\Status\Member;

use App\Http\Models\Members;
use App\Repositories\Status\Member\PerformanceListRepositoryInterface;
use Illuminate\Support\Facades\DB;

class PerformanceListRepository implements PerformanceListRepositoryInterface
{
    public function execute()
    {
       return self::getMembersWithSection();
    }

    public function getMembersWithSection()
    {
        $query = Members::query()->where(function ($query) {
            return $query->where('tb_member.deleted', '=', '0');
        });

        $subQuery = DB::table('tb_member_section')
                                  ->select('member_id', 'section_id', 'created')
                                  ->where('deleted', '=', '0')
                                  ->whereRaw('created = (
                                                  SELECT
                                                    MAX(created)
                                                  FROM
                                                    tb_member_section AS tmp_tb_member_section
                                                  WHERE tb_member_section.member_id = tmp_tb_member_section.member_id
                                                  AND tb_member_section.deleted = 0
                                              )
                                  ');
        //var_dump($subQuery->toSql()); // string(629) "select `member_id`, `section_id`, `created` from `tb_member_section` where `deleted` = ? and created = ( SELECT MAX(created) FROM tb_member_section AS tmp_tb_member_section WHERE tb_member_section.member_id = tmp_tb_member_section.member_id AND tb_member_section.deleted = 0 )"
        //exit();

        return $query->select(
                                'tb_member.member_id',
                                'tb_member.fullname',
                                'tb_member.count_burning',
                                'tb_member.count_join_project',
                                'tb_member.count_pm',
                                'tb_member.count_member',
                                'tb_member.count_sales',
                                'tb_member.skill',
                                'tb_section.section_name'
                              )->join(DB::raw('('. $subQuery->toSql() .') as ud'), function ($join) {
                                  $join->on('tb_member.member_id','=','ud.member_id');
                              })
                              ->mergeBindings($tb_member_section)
                              ->leftjoin('tb_section', function ($join) {
                                  $join->on('tb_section.section_id', '=', 'ud.section_id')
                                      ->where('tb_section.deleted', '=', '0');
                              })
                              ->groupBy('ud.member_id')
                              ->paginate(20);

        //var_dump($query->toSql()); // string(1128) "select `tb_member`.`member_id`, `tb_member`.`fullname`, `tb_member`.`count_burning`, `tb_member`.`count_join_project`, `tb_member`.`count_pm`, `tb_member`.`count_member`, `tb_member`.`count_sales`, `tb_member`.`skill`, `tb_section`.`section_name` from `tb_member` inner join (select `member_id`, `section_id`, `created` from `tb_member_section` where `deleted` = ? and created = ( SELECT MAX(created) FROM tb_member_section AS tmp_tb_member_section WHERE tb_member_section.member_id = tmp_tb_member_section.member_id AND tb_member_section.deleted = 0 )) as ud on `tb_member`.`member_id` = `ud`.`member_id` left join `tb_section` on `tb_section`.`section_id` = `ud`.`section_id` and `tb_section`.`deleted` = ? where (`tb_member`.`deleted` = ?) group by `ud`.`member_id` limit 20 offset 0"
        //exit();
    }
}

->join() 内部結合
->leftJoin() 左外部結合
->mergeBindings($tb_section) サブクエリのバインドの値をマージ

行数が少なめになりました🐱
サブクエリ使っちゃってるのでムズいが。

// モデルでリレーションを定義してあげるともっと良いです。

Query Builderの何が嬉しいの？

クエリスコープを利用することで保守がしやすくなります

<?php
namespace App\Http\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;
use DB;
use Illuminate\Support\Collection;

class Plan extends Model
{

・・・

    /**
     * 生きているデータ。論理削除されていない
     *
     * @param Builder $query
     * @return Builder
     */
    public function scopeIsNotDeleted(Builder $query)
    {
        return $query->where('deleted', '0');
    }

    /**
     * 炎上案件かどうか
     *
     * @param Builder $query
     * @return Builder
     */
    public function scopeIsBurning(Builder $query)
    {
        return $query->where('deleted', '=', 0)
                   ->where(function ($query) {
                       $query->where('is_burning', '=', 1);
                   });
    }

    /**
     * 担当か関係者として関わっている
     *
     * @param Builder $query
     * @return Builder
     */
    public function scopeisMyPlan(Builder $query, $memberId)
    {
        return $query->where('deleted', '=', 0)
                   ->where(function ($query) use($memberId) {
                        $query->orWhere('member_id', '=', $memberId)
                            ->orWhere('member_ids', 'like', '%,'.$memberId.',%');
                   });
    }

・・・

}

定義したクエリスコープは次のように利用できます

isNotDeleted(), isMyPlan(), isBurning()として利用しています。

        // 会員の集計ステータス更新
        $members = Members::query()->where(function ($query) {
            return $query->where('tb_member.deleted', '=', '0');
        })->get();

        foreach ($members as $member) {
            $countPm = Plan::isNotDeleted()->where('member_id', $member->member_id)->count();
            $countUser = Plan::isNotDeleted()->where('member_ids','like','%,'.$member->member_id.',%')->count();
            $countJoinPlan = Plan::isMyPlan($member->member_id)->count();
            $countSales = Plan::isMyPlan($member->member_id)->sum('plan_total_price');
            $countBurning = Plan::isMyPlan($member->member_id)
                                ->isBurning()
                                ->count();

            $memberInstance = Members::find($member->member_id);
            $memberInstance->count_join_project = $countJoinPlan;
            $memberInstance->count_burning = $countBurning;
            $memberInstance->count_pm = $countPm;
            $memberInstance->count_member = $countUser;
            $memberInstance->count_sales = $countSales;
            $memberInstance->save();
        }

スコープとして関数にすることでパッと見で抽象的にクエリの内容を理解しやすくなります🐱

優技録

Laravel クエリスコープを使おう！🐱 Query Scope

まずはSQLとQuery Builderの比較

SQLで書いた場合

QueryBuilderで書いた場合

Query Builderの何が嬉しいの？

関連記事 - More from my site -

コメントを残すコメントをキャンセル

まずはSQLとQuery Builderの比較

SQLで書いた場合

QueryBuilderで書いた場合

Query Builderの何が嬉しいの？

関連記事 - More from my site -

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル