Efficient way to check for overlapping ranges in a database, Laravel validation

Question

I have a model named ClientDiscountRate, which naturally corresponds to a table.

I am developing a method to allow the creation of a new ClientDiscountRate. However, I am facing a problem: the client_discount_rates table has from and to columns, and I need to ensure that the new rate does not overlap with any existing rates.

Both from and to must be integers, with from being smaller than to.

For instance, if I already have a range from 4 to 6, no overlapping ranges means the new rate cannot have a from or to value of 4, 5, or 6. It also means the new rate cannot encompass 4-6, such as having from: 3, to: 7. The new range can be before or after the existing range, but it cannot contain, be contained by, or intersect with the existing range in any way.

Moreover, I could have multiple existing ranges. So, if I have ranges 4-6 and 10-20, the overlapping check must apply the same logic to both ranges - the new range cannot overlap, contain, or be contained by either existing range.

I have written the following code, but I am unsure if it is the most efficient approach:

$from = $requestData['from'];
$to = $requestData['to'];
$matching = ClientDiscountRate::where('client_id', $client->id)
    ->where('from', '<=', $from)
    ->where('to', '>=', $from)
    ->first();
if ($matching) return response()->json(['error' => 'this range overlaps an existing one - from'], 400);
$matching = ClientDiscountRate::where('client_id', $client->id)
    ->where('from', '<=', $to)
    ->where('to', '>=', $to)
    ->first();
if ($matching) return response()->json(['error' => 'this range overlaps an existing one - to'], 400);
$matching = ClientDiscountRate::where('client_id', $client->id)
    ->where('from', '>=', $from)
    ->where('from', '<=', $to)
    ->first();
if ($matching) return response()->json(['error' => 'this range contains an existing one'], 400);
$matching = ClientDiscountRate::where('client_id', $client->id)
    ->where('to', '>=', $from)
    ->where('to', '<=', $to)
    ->first();
if ($matching) return response()->json(['error' => 'this range contains an existing one'], 400);

Is the above a sensible way to check for undesirable overlaps in the from and to fields?

Maybe these help: stackoverflow.com/questions/60401033/…

Maksim
– Maksim

2024-04-16 16:20:51 +00:00
Commented Apr 16, 2024 at 16:20 — Maksim
– Maksim, Commented Apr 16, 2024 at 16:20
sutherlandboswell.com/…

DarkBee
– DarkBee

2024-04-17 10:00:11 +00:00
Commented Apr 17, 2024 at 10:00 — DarkBee
– DarkBee, Commented Apr 17, 2024 at 10:00

N69S · Accepted Answer · 2024-04-17 10:10:44Z

2

The rule to respect is that for all the from in the database that is less or equal your $to, you have to find that all the corresponding to for those from have to be less than your $from, if it's not the case then you have an overlapping ranges.

$from = $requestData['from'];
$to = $requestData['to'];

$matching = ClientDiscountRate::where('client_id', $client->id)
    ->where('from', '<=', $to)
    ->where('to', '>=', $from)
    ->exists();

if ($matching) {
    return response()->json(['error' => 'this range overlaps an existing one'], 400);
}

edited Apr 17, 2024 at 10:10

N69S

17.3k4 gold badges25 silver badges41 bronze badges

answered Apr 17, 2024 at 9:58

Maraboc

11.1k3 gold badges43 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TKoL Over a year ago

tested, I wasn't expecting this to work because it's so minimal but... it works. I start with an existing from-to of 5-8, and I tested a new from-to of 5-5, 5-8, 8-8, 6-7, 4-9 and they all fail as overlapping, so it's perfect and much more concise than what I was doing, which was clearly overkill.

DarkBee Over a year ago

@TKoL The article I've posted below the question will help you visualize why this answer is indeed correct

TKoL Over a year ago

@DarkBee I see, nice visualisation

Collectives™ on Stack Overflow

Efficient way to check for overlapping ranges in a database, Laravel validation

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related