The client request: the database schema and data are in Japanese, as is the front end. However, the url search parameters should be, of course, in English. Why? Kanji and other non-ASCII characters need to be URL-encoded (e.g., %E6%97%A5%E6%9C%AC for 日本), and that makes for a not-so-pretty URL. It is not intuitive, and it is long. The client specifically requested shareable permalink-link URLs.
By the way, this is PHP 8.2, Laravel 11.9 (with Breeze 2.2), MySQL, Bootstrap and a pinch of HTMX to taste.
So here is the schema:
Schema::create('rooms', function (Blueprint $table) {
$table->id(); // 自動的に unsignedBigInteger になります ***auto-increment
$table->integer('room_number')->notNull();
$table->enum('smoking_status', ['喫煙', '禁煙'])->notNull()->comment('喫煙・禁煙');
$table->enum('priority', ['女性専用', '女性優先', '男性優先'])->nullable()->default('女性優先')->comment('優先事項');
$table->boolean('corporate')->nullable()->default(false)->comment('法人');
$table->enum('room_type', ['シングル', 'ツイン', 'サービスルーム', '倉庫'])->notNull()->comment('部屋タイプ');
$table->integer('year')->comment('年 (例: 2024)');
$table->enum('month', ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12'])->comment('１月〜１２月');
$table->enum('reservable_status', ['予約可能', '予約不可'])->default('予約可能')-comment('部屋が予約可能かどうかを示すステータス');
$table->boolean('is_enable')->nullable()->default(TRUE);
$table->timestamps(); // created_at, updated_at
});And here is the relevant snippet of the blade template:
{{-- Parameter Search with GET --}}
<div class="container d-flex justify-content-center mt-3 mb-0">
<div class="border rounded p-4 shadow-sm" style="width: 90%;">
<form method="GET" action="{{ route('rooms.search') }}">
<div class="row gy-2 gx-3 justify-content-center"> {{-- align-items-center --}}
<div class="col-auto" style="width: 160px;">
<label for="room-number-input" class="form-label h6">部屋番号</label>
<input type="text" class="form-control"
id="room-number-input" name="floor_room" placeholder="入力して"
pattern="\d*" oninput="this.value = this.value.replace(/[^0-9]/g, '');">
</div>
<div class="col-auto" style="width: 160px;">
<label for="smoking-input" class="form-label h6">タバコ</label>
<select id="smoking-input" name="smoking" class="form-select">
<option value="" selected>---</option>
<option value="yes">喫煙</option>
<option value="no">禁煙</option>
</select>
</div>
<div class="col-auto" style="width: 160px;">
<label for="corporate-input" class="form-label h6">法人</label>
<select id="corporate-input" name="corporate" class="form-select">
<option value="" selected>---</option>
<option value="yes">法人</option>
<option value="no">法人なし</option>
</select>
</div>
<div class="col-auto" style="width: 160px;">
<label for="reservable-input" class="form-label h6">ステータス</label>
<select id="reservable-input" name="reservable" class="form-select">
<option value="" selected>---</option>
<option value="yes">予約可能</option>
<option value="no">予約不可</option>
</select>
</div>
<div class="col-auto d-flex flex-wrap" style="width: auto;">
<div class="me-3 mb-2" style="width: 170px;">
<fieldset class="border p-2 rounded">
<legend class="w-auto px-2 h6">優先事項</legend>
<div class="form-check">
<input class="form-check-input" type="checkbox" value="women-only" id="women-only" name="priority[]" checked> {{-- laravel uses the name attribute to retrieve the value --}}
<label class="form-check-label" for="women-only">女性専用</label>
</div>
<div class="form-check">
<input class="form-check-input" type="checkbox" value="women-first" id="women-first" name="priority[]" checked>
<label class="form-check-label" for="women-first">女性優先</label>
</div>
<div class="form-check">
<input class="form-check-input" type="checkbox" value="men-first" id="men-first" name="priority[]" checked>
<label class="form-check-label" for="men-first">男性優先</label>
</div>
</fieldset>
</div>
<div class="col-auto" style="width: 170px;">
<fieldset class="border p-2 rounded">
<legend class="w-auto px-2 h6">タイプ</legend>
<div class="form-check">
<input class="form-check-input" type="checkbox" value="single" id="single" name="type[]" checked>
<label class="form-check-label" for="single">シングル</label>
</div>
<div class="form-check">
<input class="form-check-input" type="checkbox" value="twin" id="twin" name="type[]" checked>
<label class="form-check-label" for="twin">ツイン</label>
</div>
<div class="form-check">
<input class="form-check-input" type="checkbox" value="service-room" id="service-room" name="type[]" checked>
<label class="form-check-label" for="service-room">サービスルーム</label>
</div>
<div class="form-check">
<input class="form-check-input" type="checkbox" value="storage" id="storage" name="type[]" checked>
<label class="form-check-label" for="storage">倉庫</label>
</div>
</fieldset>
</div>
</div>
</div>
<div class="d-flex mb-4 mt-3 align-items-center justify-content-center text-center">
{{-- １つ目のFieldset: 今年のラジオボタン --}}
<fieldset class="border p-1 pt-3 rounded mx-3" style="min-height: 100px;">
<div class="align-items-center">
@php
$current_year = $current_year ?? date('Y');
$current_month = $current_month ?? date('n');
$selected_year_month = $selected_year_month ?? $current_year_month ?? date('Y-m');
@endphp
@for ($i = 0; $i < 12; $i++)
@php
$display_month = ($current_month + $i) > 12 ? ($current_month + $i - 12) : ($current_month + $i);
$display_year = ($current_month + $i) > 12 ? ($current_year + 1) : $current_year;
$year_month_value = $display_year . '-' . str_pad($display_month, 2, '0', STR_PAD_LEFT); // Format as "YYYY-MM"
@endphp
<input type="radio" class="btn-check m-0.5" name="year_month_radio" id="month-{{ $year_month_value }}" value="{{ $year_month_value }}" autocomplete="off"
{{ $selected_year_month == $year_month_value ? 'checked' : '' }} onchange="clearDropdown()">
<label class="btn {{ $selected_year_month == $year_month_value ? 'btn-primary' : 'btn-outline-secondary' }} mb-2" for="month-{{ $year_month_value }}">
<small><span style="font-size: 0.75em;">{{ $display_year }}年</span><br>{{ $display_month }}月</small>
</label>
@endfor
</div>
</fieldset>
{{-- ２つ目のFieldset: 他の年月Select --}}
<fieldset class="border p-1 rounded" style="max-height: 100px; min-width: 120px;">
<div class="d-flex mb-4 mt-2 justify-content-center">
<div class="d-flex align-items-center">
<span>
<label for="year_month_dropdown" class="form-label me-2">年月</label><br>
<select id="year_month_dropdown" name="year_month_dropdown" class="form-select form-select-sm" style="max-width: 100px;" onchange="clearRadio()">
<option value="">--</option>
@for ($y = date('Y'); $y <= date('Y') + 2; $y++)
@for ($m = 1; $m <= 12; $m++)
<option value="{{ $y . '-' . str_pad($m, 2, '0', STR_PAD_LEFT) }}">
{{ $y }}年 {{ $m }}月
</option>
@endfor
@endfor
</select>
</span>
</div>
</div>
</fieldset>
</div>
<div class="d-flex justify-content-end mt-4">
<a href="{{ route('rooms.index') }}" class="btn btn-secondary me-2"> {{-- simply reloads the entire page --}}
リセット
</a>
<x-primary-button class="btn btn-info"> {{-- submits the form --}}
検索
</x-primary-button>
</div>
</form>
</div>
</div>Which looks like this:
Build the custom URL parameters so we can get a nice search URL. Note that there is no URL parameter for ‘type’ (タイプ) because if all are selected, that’s the default. So we keep the URL minimal.
The parameters can also be an “array” like this:
The client also had requested the UI to have large monthly buttons that always start with the current month, but only for the first year. The drop-down select box has past and future selections, so you can filter and view room data from any time. Of course, the user can only select one; a combined ‘radio button and select box’ input. Not sure where the UX gods stand on this one.
The ‘room number’ (部屋番号) can also be search by LIKE, so the user can find all rooms on the 9th floor, for example.
Of course, the database query must be in Japanese, hence the mapping ‘associative array.’ Funnily, coming from Python, I still call it a dictionary.
And here is the function which makes all the magic happen, with some comments to help you see what’s happening.
public function search(Request $request)
{
// Maps for English to Japanese conversion for database query
$smoking_status_map = ['yes' => '喫煙', 'no' => '禁煙'];
$priority_map = ['women-only' => '女性専用', 'women-first' => '女性優先', 'men-first' => '男性優先'];
$corporate_map = ['yes' => 1, 'no' => 0];
$room_type_map = ['single' => 'シングル', 'twin' => 'ツイン', 'service-room' => 'サービスルーム', 'storage' => '倉庫'];
$reservable_status_map = ['yes' => '予約可能', 'no' => '予約不可'];
$room_number = $request->input('floor_room');
$smoking_status_key = $request->input('smoking');
$corporate_key = $request->input('corporate');
$priority_keys = is_array($request->input('priority')) ? $request->input('priority') : explode(',', $request->input('priority', ''));
$room_type_keys = is_array($request->input('type')) ? $request->input('type') : explode(',', $request->input('type', ''));
$reservable_status_key = $request->input('reservable');
$year_month = $request->input('year_month_radio') ?? $request->input('year_month_dropdown');
$year = null;
$month = null;
if (!empty($year_month)) {
$parts = explode('-', $year_month); // Split "YYYY-MM" format
$year = $parts[0] ?? null;
$month = ltrim($parts[1], '0'); // Extract month and remove leading zero if present
}
// Form 英語→日本語
$smoking_status = $smoking_status_map[$smoking_status_key] ?? null;
$corporate = $corporate_map[$corporate_key] ?? null;
$priority_filters = array_map(fn($key) => $priority_map[$key] ?? null, $priority_keys);
$room_type_filters = array_map(fn($key) => $room_type_map[$key] ?? null, $room_type_keys);
$reservable_status = $reservable_status_map[$reservable_status_key] ?? null;
// Remove empty values from arrays
$priority_filters = array_filter($priority_filters);
$room_type_filters = array_filter($room_type_filters);
// Simple English URL
$clean_params = [];
if ($room_number !== null && $room_number !== '') {$clean_params['floor_room'] = $room_number;}
if ($smoking_status_key !== null && $smoking_status_key !== '') {$clean_params['smoking'] = $smoking_status_key;}
if ($corporate_key !== null && $corporate_key !== '') {$clean_params['corporate'] = $corporate_key;}
// if all 'priorities' are "checked": that is default, so no need to put it all in the URL parameters
$default_priorities = ['women-only', 'women-first', 'men-first'];
if (!empty(array_diff($priority_keys, $default_priorities)) ||
!empty(array_diff($default_priorities, $priority_keys))) {
$clean_params['priority'] = implode(',', $priority_keys);
}
// Do you WANT to show all 3 parameters in the URL? Use statement below.
// if (!empty($room_type_keys)) {$clean_params['type'] = implode(',', $room_type_keys);}
// if all 'room_types' are "checked": that is default, so no need to put it all in the URL parameters
$default_room_types = ['single', 'twin', 'service-room', 'storage'];
if (!empty(array_diff($room_type_keys, $default_room_types)) ||
!empty(array_diff($default_room_types, $room_type_keys))) {
$clean_params['type'] = implode(',', $room_type_keys);
}
// Do you WANT to show all 4 parameters in the URL? Use statement below.
// if (!empty($priority_keys)) {$clean_params['priority'] = implode(',', $priority_keys);}
if ($reservable_status_key !== null && $reservable_status_key !== '') {$clean_params['reservable'] = $reservable_status_key;}
if (!empty($year) && !empty($month)) {$clean_params['year_month_radio'] = $year . '-' . $month;}
// Remove empty or default parameters
$clean_params = array_filter($clean_params, function ($value) {
return $value !== null && $value !== ''; // Exclude empty or null values
});
// Manually construct the query string to avoid automatic encoding (commas are , and not %2C)
$custom_query_string = http_build_query($clean_params);
$custom_query_string = str_replace('%2C', ',', $custom_query_string);
if ($request->query() != $clean_params) {
return redirect()->to(route('rooms.search') . '?' . $custom_query_string);
}
// DB query スタート
$query = Room::where('is_enable', 1);
if (!empty($room_number)) {$query->where('room_number', 'LIKE', $room_number . '%');}
if (!empty($smoking_status)) {$query->where('smoking_status', $smoking_status);}
if ($corporate !== null && $corporate !== '') {$query->where('corporate', $corporate);}
if (!empty($priority_filters)) {$query->whereIn('priority', $priority_filters);}
if (!empty($room_type_filters)) {$query->whereIn('room_type', $room_type_filters);}
if (!empty($reservable_status)) {$query->where('reservable_status', $reservable_status);}
if (!empty($year) && !empty($month)) {$query->where('year', $year)->where('month', $month);}
// results
$rooms = $query->orderBy('room_number', 'desc')->get();
return view('room.index', compact('rooms', 'year', 'month'));
}Here’s me learning about array_diff and comparing both ways:
Oh yeah, there’s a note about implode() usage and a memo-to-self to improve my URLs a bit more. So I did:
Yep, that is a perfect URL with search parameters.
Finally, this is what the user can see. Oh yeah, there’s some fancy in-line HTMX editing capabilities, too.
Originally, I made the entire search/filter function with HTMX, but the client didn’t like that the URL remained unchanged; they specifically requested a “GET method.”
The requested step is this: in real world time when it becomes the first of the month (00:01 November 1st, 2024, for example) on the server, to insert “default room data” (which I already have in a separate table) to create data for the month of October 2025. This way we don’t need to create several years in the future worth of room data, and then go in and change each one manually if the irl characteristics change with any rooms.
