LaravelのkeyByを使用した複数テーブルデータの統合手法

バイトで開発している際に、LaravelのkeyBy機能を使用した処理を学んだので、まとめておきます。

重要なポイント

1. 大きな結合クエリを避け、必要なデータを個別に取得
2. keyByでデータを連想配列化し、効率的にアクセス可能に

テーブル構成

1. appointments

- id
- patient_name
- reservation_date
- medical_id    //medicalテーブルにつながる外部キー
- status
- created_at

2. doctors

- id
- name
- department_id   //departmentテーブルにつながる外部キー
- email
- created_at

3. departments

- id
- name
- medical_id //medicalテーブルにつながる外部キー
- location
- created_at

4. medicals

- id
- name

• appointments は doctors への外部キーを持っていない
• appointments の medical_id と departments の medical_idが一致しないといけない

シチュエーション

指定された診察予約日時期間の予約情報を出す

//入力値
researvation_date_from: 'YYYY-mm-dd',
reservation_date_to: 'YYYY-mm-dd'

//返り値
[{
	patient_name: '',
	medical_id: '',
	medical_name: '',
	reservation_date: '',
	docter_name: '',
	department_name: '',
}]

従来の方法

SELECT
    a.patient_name,
    a.reservation_date
    a.medical_id
    m.name as medical_name
    d.name as doctor_name,
    dep.name as department_name
FROM appointments a
JOIN medical m ON m.id = a.medical_id
JOIN departments dep ON dep.medical_id = a.medical_id
JOIN doctors d ON d.department_id = dep.id
WHERE a.reservation_date BETWEEN reservation_date_from and reservation_date_to

問題点：複数テーブルの結合による処理負荷

提案する解決方法

use App\Models\Appointment;
use Illuminate\Support\Facades\DB;

$params = [
    'reservation_date_from' => 'YYYY-mm-dd',
    'reservation_date_to' => 'YYYY-mm-dd'
];

class AppointmentService
{
    public function __construct(Appointment $model)
    {
        $this->model = $model;
    }
    
    public function search($params = [])
    {
        $query = $this->model->newQuery();
        
        // 2つの独立したクエリに分割
        $appointments = $query->select(
            'appointments.patient_name',
            'appointments.reservation_date',
            'appointments.medical_id',
            'medical.name as medical_name'
        )
        ->join('medical', 'medical.id', '=', 'appointments.medical_id')
        ->whereBetween('appointments.reservation_date', [
            $params['reservation_date_from'],
            $params['reservation_date_to']
        ])
        ->orderBy('medical_id')
        ->get();
        
        $doctors = DB::table('doctor')
            ->select(
                'doctors.name AS doctor_name',
                'departments.name AS department_name',
                'departments.medical_id'
            )
            ->join('departments', 'departments.id', '=', 'doctors.department_id')
            ->get()
            ->keyBy('medical_id');
        // keyByによる効率的なデータアクセス
        
        // データの統合
        $reservation_details = $appointments->map(function ($appointment) use ($doctors) {
            $medical_id = $appointment->medical_id;
            
            // appointments と doctors の直接結合を避け、medical_id を介して関連付け
            $doctor_info = $doctors[$medical_id];
            
            return [
                'patient_name' => $appointment->patient_name,
                'reservation_date' => $appointment->reservation_date,
                'medical_id' => $appointment->medical_id,
                'medical_name' => $appointment->medical_name,
                'doctor_name' => $doctor_info->doctor_name,
                'department_name' => $doctor_info->department_name
            ];
        });
        
        return $reservation_details;
    }
}

ポイント① : 大きな結合クエリを避け、必要なデータを個別に取得

$appointments = 
		$query->select(
		'appointments.patient_name', 
		'appointments.reservation_date', 
		'appointments.medical_id',
		'medical.name as medical_name'
		)
		->join('medical', 'medical.id', '=', 'appointments.medical_id')
		->whereBetween('appointments.reservation_date', [
		$params['reservation_date_from'], $params['reservation_date_to']
		])
		->orderBy('medical_id')
		->get();
		
		$doctors = DB::table(doctor)
		->select(
			'doctors.name AS doctor_name', 
			'departments.name AS department_name', 
			'departments.medical_id'
		)
		->join('departments', 'departments.id', '=', ',doctors.department_id')
		->get()
		->keyBy('medical_id');

ポイント②: keyByでデータを連想配列化し、効率的にアクセス可能に

keyByによる効率的なデータアクセス

->get()->keyBy('medical_id');

• doctors データを medical_id でインデックス化
• ループ処理での検索を効率化

間接的な結合条件の処理

// appointments と doctors の直接結合を避け、medical_id を介して関連付け
$doctor_info = $doctors[$medical_id];

• appointments と doctors テーブルに直接の関連がなくても、medical_id を介して関連付けが可能
• これにより、複雑な結合条件を回避

まとめ

従来の方法

4テーブル結合による単一クエリ

• データ量が増えるほど処理時間が増加
• メモリ使用量が大きい

提案方法

2つのクエリ + keyByによる統合

• データ量が増えても比較的安定した処理時間
• メモリ使用効率が良い