FaunaDB get entries by date range with index binding not working

Question

I am struggling to get an Index by Date to work with a Range.

I have this collection called orders:

CreateCollection({name: "orders"})

And I have these sample entries, with one attribute called mydate. As you see it is just a string. And I do need to create the date as a string since in my DB we already have around 12K records with dates like that so I cant just start using the Date() to create them.

Create(Collection("orders"), {data: {"mydate": "2020-07-10"}})
Create(Collection("orders"), {data: {"mydate": "2020-07-11"}})
Create(Collection("orders"), {data: {"mydate": "2020-07-12"}})

I have created this index that computes the date to and actual Date object

CreateIndex({
  name: "orders_by_my_date",
  source: [
    {
      collection: Collection("orders"),
      fields: {
        date: Query(Lambda("order", Date(Select(["data", "mydate"], Var("order"))))),
      },
    },
  ],
  terms: [
    {
      binding: "date",
    },
  ],
});

If I try to fetch a single date the index works.

// this works
Paginate(
  Match(Index("orders_by_my_date"), Date("2020-07-10"))
);
// ---
{
  data: [Ref(Collection("orders"), "278496072502870530")]
}

But when I try to get a Range it never finds data.

// This does NOT work :(
Paginate(
  Range(Match(Index("orders_by_my_date")), Date("2020-07-09"), Date("2020-07-15"))
);
// ---
{
  data: []
}

Why the index does not work with a Range?

Answer 1

Range operates on the values of an index, not on the terms.

See: https://docs.fauna.com/fauna/current/api/fql/functions/range?lang=javascript

You need to change your index definition to:

CreateIndex({
  name: "orders_by_my_date",
  source: [
    {
      collection: Collection("orders"),
      fields: {
        date: Query(Lambda("order", Date(Select(["data", "mydate"], Var("order"))))),
      },
    },
  ],
  values: [
    { binding: "date" },
    { field: ["ref"] },
  ],
})

Then you can get the results that you expect:

> Paginate(Range(Match(Index('orders')), Date('2020-07-11'), Date('2020-07-15')))
{
  data: [
    [
      Date("2020-07-11"),
      Ref(Collection("orders"), "278586211497411072")
    ],
    [
      Date("2020-07-12"),
      Ref(Collection("orders"), "278586213229658624")
    ],
    [
      Date("2020-07-13"),
      Ref(Collection("orders"), "278586215000703488")
    ],
    [
      Date("2020-07-14"),
      Ref(Collection("orders"), "278586216887091712")
    ],
    [
      Date("2020-07-15"),
      Ref(Collection("orders"), "278586218585784832")
    ]
  ]
}

Answer 2

There are some mistakes here, first of all, you have to create documents that way:

Create(Collection("orders"), {data: {"mydate": ToDate("2020-07-10")}})

The index has to be created like this:

CreateIndex(
    {
     name: "orders_by_my_date",
     source: Collection("orders"),
     values:[{field:['data','mydate']},{field:['ref']}]
    }
)

and finally, you can query your index and range:

Paginate(Range(Match('orders_by_my_date'),[Date("2020-07-09")], [Date("2020-07-15")]))
{ data:
   [ [ Date("2020-07-10"),
       Ref(Collection("orders"), "278532030954734085") ],
     [ Date("2020-07-11"),
       Ref(Collection("orders"), "278532033804763655") ],
     [ Date("2020-07-12"),
       Ref(Collection("orders"), "278532036737630725") ] ] }

or if you want to get the full doc:

Map(Paginate(Range(Match('orders_by_my_date'),[Date("2020-07-09")], [Date("2020-07-15")])),Lambda(['date','ref'],Get(Var('ref'))))
{ data:
   [ { ref: Ref(Collection("orders"), "278532030954734085"),
       ts: 1601887694290000,
       data: { mydate: Date("2020-07-10") } },
     { ref: Ref(Collection("orders"), "278532033804763655"),
       ts: 1601887697015000,
       data: { mydate: Date("2020-07-11") } },
     { ref: Ref(Collection("orders"), "278532036737630725"),
       ts: 1601887699800000,
       data: { mydate: Date("2020-07-12") } } ] }

Answer 3

Another alternative is to use a filter with a lambda expression to validate which values you want

Filter(
  Paginate(Documents(Collection('orders'))),
  Lambda('order',
    And(
      GTE(Select(['data', 'mydate'], Var('order')), '2020-07-09'),
      LTE(Select(['data', 'mydate'], Var('order')), '2020-07-15')
      )
    )
  )

You can update the conditions as you need

I believe this will work with the strings you have already