We build booking service and dates ranges is the main frequently requested information. And we have difficulties to find not expensive solution to operate with dates ranges.
At now we store date range by 2 fields dateFrom and dateTo.
Let’s look at a small example which is causing the problem:
Suppose we have booking (order) from 2021-07-01T07:00:00Z to 2021-07-10T07:00:00Z. User request vehicle status, provide dates range 2021-07-05T07:00:00Z and 2021-07-07T07:00:00Z (between dateFrom and dateTo).
What is solution to return right status in this case “not available”.
in documentation we find next solution to work with dates, which based on use “q.Range”:
indexes:
{
name: 'orders_by_vehicle_and_dateFrom',
serialized: true,
source: q.Collection('orders'),
terms: [{ field: ['data', 'vehicle'] }],
values: [
{ field: ['data', 'dateFrom'] },
{ field: 'ref' },
],
}
{
name: 'orders_by_vehicle_and_dateTo',
serialized: true,
source: q.Collection('orders'),
terms: [{ field: ['data', 'vehicle'] }],
values: [
{ field: ['data', 'dateTo'] },
{ field: 'ref' },
],
}
Then we doing request (simplified) :
q.Range(
q.Match(q.Index('orders_by_vehicle_and_dateFrom'), q.Ref(q.Collection('vehicles'), vehicle.id)),
[q.Time( 2021-07-05T07:00:00Z), q.Ref(q.Collection('vehicles'), vehicle.id)],
[q.Time( 2021-07-07T07:00:00Z), q.Ref(q.Collection('vehicles'), vehicle.id)],
)
AND (Omitting different conversions Paginate, Select, Distinct, etc...)
q.Range(
q.Match(q.Index('orders_by_vehicle_and_dateTo'), q.Ref(q.Collection('vehicles'), vehicle.id)),
[q.Time(2021-07-05T07:00:00Z), q.Ref(q.Collection('vehicles'), vehicle.id)],
[q.Time( 2021-07-07T07:00:00Z), q.Ref(q.Collection('vehicles'), vehicle.id)],
)
This solution not work because we looking dateFrom and dateTo beween 2021-07-05T07:00:00Z, 2021-07-07T07:00:00Z, and order where dateFrom: Time(“2021-07-01T07:00:00Z”), dateTo: Time(“2021-07-10T07:00:00Z”) never will be matched.
Another solution is collect all orders for given vehicle and then filter it to find which orders belong to given dates range like (Part of the request):
q.Filter(q.Var('vehicleOrders'),
q.Lambda('vehicleOrder',
q.Or(
q.And(
q.GTE(q.Var('from'), q.Select(['data', 'dateFrom'], q.Get(q.Var('vehicleOrder')))),
q.LTE(q.Var('to'), q.Select(['data', 'dateTo'], q.Get(q.Var('vehicleOrder')))),
),
q.And(
q.LTE(q.Var('from'), q.Select(['data', 'dateFrom'], q.Get(q.Var('vehicleOrder')))),
q.GTE(q.Var('to'), q.Select(['data', 'dateFrom'], q.Get(q.Var('vehicleOrder')))),
),
q.And(
q.LTE(q.Var('from'), q.Select(['data', 'dateTo'], q.Get(q.Var('vehicleOrder')))),
q.GTE(q.Var('to'), q.Select(['data', 'dateTo'], q.Get(q.Var('vehicleOrder')))),
),
q.And(
q.LTE(q.Var('from'), q.Select(['data', 'dateFrom'], q.Get(q.Var('vehicleOrder')))),
q.GTE(q.Var('to'), q.Select(['data', 'dateTo'], q.Get(q.Var('vehicleOrder')))),
),
)))
This solution work but looking very expensive, because we collect all orders for vehicle for all time, it may be many documents.
The questions is
"How to solve this problem with in right way, to economy resources?" or
"How correctly determine that a date belongs to a range?" or
“How to store date ranges correctly?”