Date ranges overlap query

Help
,
2

Hi @msign!

After digging, and finding some past mistakes, I worked it out and also confirmed it’s the same as another shared a while ago.

The idea here is that you want to do 2 different queries, and then find the Intersection of results:

  1. all docs where start is within a range
  2. all docs where end is within a range

Indexes for Range queries

Here are two Indexes to do that:

CreateIndex({
  name: "tracks_sortedBy_start",
  unique: false,
  serialized: true,
  source: Collection("tracks"),
  values: [
    { field: ["data", "start"] }, // will use the Range function over this
    { field: ["ref"] } // this is important for the join
  ]
})

// check by itself
Paginate(
  Range(
    Match(Index("tracks_sortedBy_start")),
    [1614730066522],
    [1614730067577]
  )
)

CreateIndex({
  name: "tracks_sortedBy_end",
  unique: false,
  serialized: true,
  source: Collection("tracks"),
  values: [
    { field: ["data", "end"] }, // will use the Range function over this
    { field: ["ref"] } // this is important for the join
  ]
})

// check by itself
Paginate(
  Range(
    Match(Index("tracks_sortedBy_end")),
    [1614730066522],
    [1614730067577]
  )
)

Use Join to remove date values

We cannot Intersect them directly, though – The results are not for the same fields. We need another Index to Join and remove the date information. Then we can take the Intersection of the two sets.

CreateIndex({
  name: "tracks_by_ref",
  unique: false,
  serialized: true,
  source: Collection("tracks"),
  terms: [
    { field: ["ref"] }
  ],
  values: [
    { field: ["ref"] }
  ]
})

// example
Paginate(
  Join(
    Range(Match(Index("tracks_sortedBy_start")), [1614730066522], [1614730067577]),
    Lambda(["start", "ref"], Match("tracks_by_ref", Var("ref")))
  )
)

Use Intersection to finish the whole query

Paginate(
  Intersection(
    Join(
      Range(
        Match(Index("tracks_sortedBy_start")), 
        [/*earliest start*/], 
        [/*latest start*/]
      ),
      Lambda(["start", "ref"], Match("tracks_by_ref", Var("ref")))
    ),
    Join(
      Range(
        Match(Index("tracks_sortedBy_end")), 
        [/*earliest end*/], 
        [/*latest end*/]
      ),
      Lambda(["start", "ref"], Match("tracks_by_ref", Var("ref")))
    )
  )
)
2 Likes