Date ranges overlap query

[msign]

Hello,
I am new to FaunaDB and struggling with one query.

I would like to get documents that are within a certain date range.

I have documents that have start and end fields, these are timestamps.

The query could work with max(user.start, document.start) <= min(user.end, document.end) but I am struggling to create the right query.

For example:
Document has Start 10 Oct 2020 and End 11 Oct 2020

User selects a range to search for:
Start 10 Oct 2020 End 10 Oct 2020 - returns the document
Start 9 Oct to 2020 End 10 Oct 2020 - returns the document
Start 9 Oct to 2020 End 12 Oct 2020 - returns the document
Start 10 Oct 2020 End 12 Oct 2020 - returns the document
Start 12 Oct 2020 End 14 Oct 2020 - does not return the document

Document can look like this:
{
“id”: “5443aa42-b79a-4725-a972-37442894d4f1”,
“type”: “track”,
“start”: 1614730066522,
“end”: 1614730067577
}

Thanks a lot, I am open to any suggestions when it comes to structure, e.g. creating some helper tables if the above is not possible, etc.

FYI I have read similar discussion (Indexing on Dates, Conflict-Checking for Appointments - #3 by databrecht) but I am still lost.

[ptpaterson]

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")))
    )
  )
)

[msign]

Hi Paul,
just had the time to implement this in production, sorry for the delay. Thank you very much, this is exactly what I was looking for and it works well!

Appreciate it!