How to select all documents where one date field is after another date field in FaunaDB

Question

I have a very simple collection with documents that look like this:

{
  ...
  latestEdit: Time(...),
  lastPublished: Time(...)
}

I would like to query all documents that have a latestEdit time that's after lastPublished time.

I find FQL to be very different to SQL and I'm finding the transition quite hard.

Any help much appreciated.

Answer 1

Fauna's FQL is not declarative, so you have to construct the appropriate indexes and queries to help you solve problems like this.

Fauna indexes have a feature called "bindings", which allow you to provide a user-defined function that can compute a value based on document values. The binding lets us index the computed value by itself (rather than having to index on latestEdit or lastPublished). Here's what that might look like:

CreateIndex({
  name: "edit_after_published",
  source: {
    collection: Collection("test"),
    fields: {
      needsPublish: Query(
        Lambda(
          "doc",
          Let(
            {
              latestEdit: Select(["data", "latestEdit"], Var("doc")),
              lastPublished: Select(["data", "lastPublished"], Var("doc")),
            },
            If(
              GT(Var("latestEdit"), Var("lastPublished")),
              true,
              false
            )
          )
        )
      )
    }
  },
  terms: [ { binding: "needsPublish" } ]
})

You can see that we define a binding called needsPublish. The binding uses Let to define named values for the two document fields that we want to compare, and then the If statement checks to see if the latestEdit value is greather than lastPublished value: when it is we return true, otherwise we return false. Then, the binding is used in the index's terms definition, which defines the fields that we want to be able to search on.

I created sample documents in a collection called test, like so:

> Create(Collection("test"), { data: { name: "first", latestEdit: Now(), lastPublished: TimeSubtract(Now(), 1, "day") }})
{
  ref: Ref(Collection("test"), "306026106743423488"),
  ts: 1628108088190000,
  data: {
    name: 'first',
    latestEdit: Time("2021-08-04T20:14:48.121Z"),
    lastPublished: Time("2021-08-03T20:14:48.121Z")
  }
}
> Create(Collection("test"), { data: { name: "second", lastPublished: Now(), latestEdit: TimeSubtract(Now(), 1, "day") }})
{
  ref: Ref(Collection("test"), "306026150784664064"),
  ts: 1628108130150000,
  data: {
    name: 'second',
    lastPublished: Time("2021-08-04T20:15:30.148Z"),
    latestEdit: Time("2021-08-03T20:15:30.148Z")
  }
}

The first document subtracts one day from lastPublished and the second document subtracts one day from latestEdit, to test both conditions of the binding.

Then we can query for all documents where needsPublish results in true:

> Map(Paginate(Match(Index("edit_after_published"), true)), Lambda("X", Get(Var("X"))))
{
  data: [
    {
      ref: Ref(Collection("test"), "306026106743423488"),
      ts: 1628108088190000,
      data: {
        name: 'first',
        latestEdit: Time("2021-08-04T20:14:48.121Z"),
        lastPublished: Time("2021-08-03T20:14:48.121Z")
      }
    }
  ]
}

And we can also query for all documents where needsPublish is false:

> Map(Paginate(Match(Index("edit_after_published"), false)), Lambda("X", Get(Var("X"))))
{
  data: [
    {
      ref: Ref(Collection("test"), "306026150784664064"),
      ts: 1628108130150000,
      data: {
        name: 'second',
        lastPublished: Time("2021-08-04T20:15:30.148Z"),
        latestEdit: Time("2021-08-03T20:15:30.148Z")
      }
    }
  ]
}