Question 364736 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Checking existence of data by make, model, and year range

I am receiving inputs for ‘make’, ‘model’, ‘fromyear’, and ‘toyear’ from the user. I want to prevent the user from entering a ‘fromyear’ and ‘toyear’ range that already exists in the database for the given ‘make’ and ‘model’.

For example, if there is a record in the database with ‘make’ as ‘Toyota’, ‘model’ as ‘Camry’, ‘fromyear’ as 2015, and ‘toyear’ as 2020, then the user should not be allowed to enter the following ranges:

  • 2016 to 2017
  • 2016 to 2018
  • 2019 to 2022
  • 2013 to 2016

However, if the ‘fromyear’ and ‘toyear’ do not fall within any existing range in the database, then the user should be allowed to enter them.

The database table has columns named ‘make’, ‘model’, ‘fromyear’, and ‘toyear’.

$has_range = PartsList::where('make', $request->make)
    ->where('model', $request->model)
    ->where(function ($query) use ($request) {
        $query->whereBetween('fromyear', [$request->fromyear, $request->toyear])
            ->orWhereBetween('toyear', [$request->fromyear, $request->toyear]);
    })
    ->exists();

if ($has_range) {
    $msg = 'year range already exists for this make and model';
}
Tags:

2

Answers


  1. 0

    You can use the where and orWhere methods in combination with the whereBetween method to check if a record exists with the given make, model, and year range. The whereBetween method checks if a column’s value is between two values.

    $has_range = PartsList::where('make', $request->make)
    ->where('model', $request->model)
    ->where(function ($query) use ($request) {
        $query->where(function ($query) use ($request) {
            $query->where('fromyear', '<=', $request->fromyear)
                ->where('toyear', '>=', $request->fromyear);
        })->orWhere(function ($query) use ($request) {
            $query->where('fromyear', '<=', $request->toyear)
                ->where('toyear', '>=', $request->toyear);
        });
    })
    ->exists();

if ($has_range) {
    $msg = 'year range already exists for this make and model';
}
    Login or Signup to reply.
  2. 0

    To ensure that any kind of overlap is caught, you need to adjust your logic to consider all possible overlap scenarios:

    1. The user’s fromyear is within any existing range.
    2. The user’s toyear is within any existing range.
    3. The user’s range entirely encompasses an existing range.
    4. An existing range entirely encompasses the user’s range.

    Here’s how you can adjust your query to cover all these cases:

    $has_range = PartsList::where('make', $request->make)
    ->where('model', $request->model)
    ->where(function ($query) use ($request) {
        // Case 1: User's fromyear is within an existing range.
        $query->where(function($q) use ($request) {
            $q->where('fromyear', '<=', $request->fromyear)
              ->where('toyear', '>=', $request->fromyear);
        })
        // Case 2: User's toyear is within an existing range.
        ->orWhere(function($q) use ($request) {
            $q->where('fromyear', '<=', $request->toyear)
              ->where('toyear', '>=', $request->toyear);
        })
        // Case 3: User's range encompasses an existing range.
        ->orWhere(function($q) use ($request) {
            $q->where('fromyear', '>=', $request->fromyear)
              ->where('toyear', '<=', $request->toyear);
        })
        // Case 4: An existing range encompasses the user's range (this might actually be redundant given the first two checks, but added for clarity).
        ->orWhere(function($q) use ($request) {
            $q->where('fromyear', '<=', $request->fromyear)
              ->where('toyear', '>=', $request->toyear);
        });
    })
    ->exists();

if ($has_range) {
    $msg = 'The year range already exists for this make and model.';
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search