はじめに
今回はLaravelのDBファザードを使ってデータを取得する。
web.phpを使用しアクセスする。
web.php
web.php
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\ProjectWorkController;←追加
Route::get('/project_work', [ProjectWorkController::class, 'index']);←追加
コントローラ作成
php artisan make:controller ProjectWorkController
app/Http/Controllers/ProjectWorkController.php
DBファザードで、生のSQLクエリを実行し、DB::selectを使用する。
ProjectWorkController.php
<?php
namespace App\Http\Controllers;
use App\Models\ProjectWork;
use Illuminate\Support\Facades\DB;
class ProjectWorkController extends Controller
{
public function index()
{
$results = ProjectWork::getWorkHoursPerProjectInAMonth('2023-06-01', '2023-12-30',);
dd($results);
}
}
モデル作成
前回作成したSQLをそのまま利用する。
php artisan make:model ProjectWork
前回作成したSQLをDB::select()を使用し、貼り付ければSQLをそのまま実行できる。
ProjectWork.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
class ProjectWork extends Model
{
use HasFactory;
protected $table = 'project_works';
public static function getWorkHoursPerProjectInAMonth($startDate, $endDate)
{
return DB::select(
"
-- 実績値
(
SELECT
-- sum_hoursサブクエリから各フィールドを選択
sum_hours.employee_id,
sum_hours.Month as month,
sum_hours.project_code,
sum_hours.budget_actual_flag,
sum_hours.total_work_hours,
-- 各プロジェクトの作業時間を、その月の従業員の総作業時間で割ることで比率を算出
ROUND(sum_hours.total_work_hours / total_hours.total_work_hours, 2) as ratio
FROM
(
-- sum_hoursサブクエリでは、各従業員、プロジェクト、budget_actual_flag、月ごとの作業時間の合計を算出
SELECT
employee_id,
project_code,
budget_actual_flag,
DATE_FORMAT(work_date, '%Y%m') as Month,
SUM(work_hours) as total_work_hours
FROM
project_works
WHERE
work_date >= ? AND work_date <= ?
and budget_actual_flag = 1
GROUP BY
employee_id,
project_code,
budget_actual_flag,
DATE_FORMAT(work_date, '%Y%m')
) as sum_hours
INNER JOIN
(
-- total_hoursサブクエリでは、各従業員、月ごとの総作業時間の合計を算出
SELECT
employee_id,
DATE_FORMAT(work_date, '%Y%m') as Month,
SUM(work_hours) as total_work_hours
FROM
project_works
WHERE
work_date >= ? AND work_date <= ?
and budget_actual_flag = 1
GROUP BY
employee_id,
DATE_FORMAT(work_date, '%Y%m')
) as total_hours
ON
-- 2つのサブクエリをemployee_idと月で結合
sum_hours.employee_id = total_hours.employee_id AND
sum_hours.Month = total_hours.Month
)
UNION ALL
-- 予定値
(
SELECT
-- sum_hoursサブクエリから各フィールドを選択
sum_hours.employee_id,
sum_hours.Month as month,
sum_hours.project_code,
sum_hours.budget_actual_flag,
sum_hours.total_work_hours,
-- 各プロジェクトの作業時間を、その月の従業員の総作業時間で割ることで比率を算出
ROUND(sum_hours.total_work_hours / total_hours.total_work_hours, 2) as ratio
FROM
(
-- sum_hoursサブクエリでは、各従業員、プロジェクト、budget_actual_flag、月ごとの作業時間の合計を算出
SELECT
employee_id,
project_code,
budget_actual_flag,
DATE_FORMAT(work_date, '%Y%m') as Month,
SUM(work_hours) as total_work_hours
FROM
project_works
WHERE
work_date >= ? AND work_date <= ?
and budget_actual_flag = 0
GROUP BY
employee_id,
project_code,
budget_actual_flag,
DATE_FORMAT(work_date, '%Y%m')
) as sum_hours
INNER JOIN
(
-- total_hoursサブクエリでは、各従業員、月ごとの総作業時間の合計を算出
SELECT
employee_id,
DATE_FORMAT(work_date, '%Y%m') as Month,
SUM(work_hours) as total_work_hours
FROM
project_works
WHERE
work_date >= ? AND work_date <= ?
and budget_actual_flag = 0
GROUP BY
employee_id,
DATE_FORMAT(work_date, '%Y%m')
) as total_hours
ON
-- 2つのサブクエリをemployee_idと月で結合
sum_hours.employee_id = total_hours.employee_id AND
sum_hours.Month = total_hours.Month
)
ORDER BY
employee_id,
Month,
budget_actual_flag,
project_code;
",
[
$startDate,
$endDate,
$startDate,
$endDate,
$startDate,
$endDate,
$startDate,
$endDate,
]
);
}
}
コメントの書き方注意する。
--を使用して、-- コメントのように--の後にスペースが必要
結果例
DB::selectは結果としてオブジェクトの配列を返す。
例）
array:777 [▼ // app/Http/Controllers/ProjectWorkController.php:129
0 => {#294 ▶
+"employee_id": "EMP001"
+"month": "202306"
+"project_code": "PRJ001"
+"budget_actual_flag": 0
+"total_work_hours": "8.00"
+"ratio": "0.33"
}
1 => {#296 ▶
+"employee_id": "EMP001"
+"month": "202306"
+"project_code": "PRJ002"
+"budget_actual_flag": 0
+"total_work_hours": "7.00"
+"ratio": "0.29"
}