How to make unique column validation in laravel along with another column?

Question

Validation

I have a sessions table which has a "room_id" column and "start" and "end" date columns.
New values ​​need to be added to the table so that each room_id has unique start and end values.
How to do it?
After all, when I simply write unique in the validation, then laravel validates ALL date values, regardless of the room_id, and this is not necessary.

Please help, I really really need this!

Answer 1

first, fix your structure, make the fields start / end: timestamp or datetime so that they store the hours too, otherwise, closing the session one day, you will not be able to reopen the session with your check on the same day
; secondly, where do you want to validate in the database or in Laravel, I see a screenshot from the base here.
if you have Postgres, you can control it at the database level by adding an Exclude constraint

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\Schema;
use Umbrellio\Postgres\Schema\Blueprint;

class UpdateSessionsTable extends Migration
{
    public function up()
    {
        Schema::table('sessions', function (Blueprint $table) {
            $table->datetime('start')->change();
            $table->datetime('end')->change();
            $table
                ->exclude(['room_id', 'start', 'end'])
                ->method('gist')
                ->using('daterange(start, end)', '&&')
                ->using('room_id', '=');
        });
    }
    ...
}

such a migration will add the following constraint to your database

ALTER TABLE sessions
    ADD CONSTRAINT sessions_room_id_start_end_excl
        EXCLUDE USING gist (room_id WITH =, daterange(start, end) WITH &&)

support for exclude constraints in Laravel can be added using the Postgres extension:
composer require umbrellio/laravel-pg-extensions
To add validation to Laravel and be able to reuse it, you need to add a macro to Query\Builder.

use Illuminate\Database\Query\Builder;
use Illuminate\Support\ServiceProvider;

class QueryServiceProvider extends ServiceProvider
{
    public function boot()
    {
        Builder::macro(
            'overlaps',
            function ($startDate, $endDate, $startDateColumn, $endDateColumn, $bounds = '[]') {
                $expression = implode(' && ', [
                    "daterange(?::date, ?::date, '{$bounds}')",
                    "daterange({$startDateColumn}, {$endDateColumn}, '{$bounds}')",
                ]);

                return $this->whereRaw($expression, [$startDate, $endDate]);
            }
        );
    }
}

because in Request, or where you do validation through UniqueDatabaseRule, Query \ Builder is used, and in the request,
do this

use Illuminate\Foundation\Http\FormRequest;

class SaveSessionRequest extends FormRequest
{
    public function rules(): array
    {
        return [
            'session.room_id' => Rule::unique('sessions', 'room_id')->overlaps(
                $this->input('session.start'),
                $this->input('session.end'),
                'start',
                'end',
                '[]'
            ),
           // ..
        ];
    }

    // ...
}

Or if you don't want a macro, transfer this where directly to your Request
. And if you have MySQL, then you need to come up with something similar, because MySQL does not support such tricks out of the box.