Query items of a collection before a certain date?

Hello,

I looked on google and tried different thing but I cannot figure out how to query all the item of a collection that have the key dueDate before the a certain Date.
On Mysql I would do something like :
select * from table_name where dueDate < "2001-01-01 00:00:00"

That query on mysql would return the items with the Date Inferior to 2001-01-01

I tried to do use that query on Fauna : q.Map( q.Paginate( q.Match(q.Index(indexesQuery1)), { before: Date('2021-01-15T17:34:00+08:00') } ), q.Lambda("X", q.Get(q.Var("X"))) ) )
indexQuery1 is : getNewWordDemoIso(dueDate: Date!)

But It returns an empty Array,
Also I saved all my Date all Fauna the date format called : iso 8601
Also Im using javascript
Any ideas !?

thanks !!

Hi @romain130492 and welcome!

Let suppose you have a collection like this:

Map(Paginate(Documents(Collection('dueDate'))),Lambda('x',Get(Var('x'))))
{ data:
   [ { ref: Ref(Collection("dueDate"), "287778517301592581"),
       ts: 1610705830775000,
       data: { id: 1, dueDate: Time("2021-01-15T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631528219141"),
       ts: 1610705939711200,
       data: { id: 20, dueDate: Time("2021-01-15T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631528220165"),
       ts: 1610705939710600,
       data: { id: 14, dueDate: Time("2021-01-09T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631528221189"),
       ts: 1610705939712500,
       data: { id: 16, dueDate: Time("2021-01-11T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631528222213"),
       ts: 1610705939710000,
       data: { id: 10, dueDate: Time("2021-01-05T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631529267717"),
       ts: 1610705939711800,
       data: { id: 12, dueDate: Time("2021-01-07T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631529267719"),
       ts: 1610705939703000,
       data: { id: 9, dueDate: Time("2021-01-04T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631529268743"),
       ts: 1610705939706000,
       data: { id: 4, dueDate: Time("2021-01-18T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631537656325"),
       ts: 1610705939713700,
       data: { id: 6, dueDate: Time("2021-01-01T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631537657349"),
       ts: 1610705939713100,
       data: { id: 7, dueDate: Time("2021-01-02T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631538704897"),
       ts: 1610705939715600,
       data: { id: 19, dueDate: Time("2021-01-14T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631538705921"),
       ts: 1610705939716200,
       data: { id: 18, dueDate: Time("2021-01-13T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631538706945"),
       ts: 1610705939718100,
       data: { id: 17, dueDate: Time("2021-01-12T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631538707969"),
       ts: 1610705939714300,
       data: { id: 15, dueDate: Time("2021-01-10T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631538708993"),
       ts: 1610705939717500,
       data: { id: 13, dueDate: Time("2021-01-08T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631538710017"),
       ts: 1610705939715000,
       data: { id: 8, dueDate: Time("2021-01-03T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631538711041"),
       ts: 1610705939716800,
       data: { id: 3, dueDate: Time("2021-01-17T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631538712065"),
       ts: 1610705939719300,
       data: { id: 5, dueDate: Time("2021-01-19T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631538713089"),
       ts: 1610705939718700,
       data: { id: 2, dueDate: Time("2021-01-16T09:34:00Z") } },
     { ref: Ref(Collection("dueDate"), "287778631544996359"),
       ts: 1610705939720000,
       data: { id: 11, dueDate: Time("2021-01-06T09:34:00Z") } } ] }

You can create an index:

CreateIndex(
  {
    name:'indexesQuery1',
    source:Collection("dueDate"),
    values:[
      {field:['data','dueDate']},
      {field:['ref']}
    ]
  }
)

and now you can:

  • get all documents before a date
Paginate(Range(Match('indexesQuery1'),[],[Time("2021-01-15T17:34:00+08:00")]))
  • get all documents after a date
Paginate(Range(Match('indexesQuery1'),[Time("2021-01-15T09:34:00Z")],[]))
  • get all documents between 2 dates
Paginate(Range(Match('indexesQuery1'),[Time("2021-01-10T09:34:00Z")],[Time("2021-01-15T09:34:00Z")]))

Hope this helps.

Luigi

Hello,
Thank you for your answer,
I tried that and it worked,
But i now get the ref of the items, is there anyway i could get the data as well ?

This is what I get

This is the index I made :

     q.CreateIndex(
    {
      name:'indexesQuery1',
      source:q.Collection("WordDemoIso"),
      values:[
        {field:['data','dueDate']},
        {field:['ref']}
      ]
    }
  )

I query the data in this way :

     q.Paginate(q.Range(q.Match('indexesQuery1'),[q.Time("2021-01-13T09:34:00Z")],[]))

This is one of the item of my db
{
“ref”: Ref(Collection(“WordDemoIso”), “287784067080913416”),
“ts”: 1610711123500000,
“data”: {
“dueDate”: Date(“2021-01-15”),
“netlifyID”: “d496d0b7-629a-4ea2-b3b7-a31989d79531”,
“wordID”: “test-7”
}
}

If you have any ideas! Thank you,
:thinking:

Hi @romain130492 ,

sure, you can that way:

Map(
  Paginate(Range(Match('indexesQuery1'),[Time("2021-01-10T09:35:00Z")],[Time("2021-01-15T09:34:00Z")])),
  Lambda(['date','ref'],Get(Var('ref')))
)

Luigi

alright it works, Thanks!
Last question,
With that query

Map(
  Paginate(Range(Match('indexesQuery1'),[Time("2021-01-10T09:35:00Z")],[Time("2021-01-15T09:34:00Z")])),
  Lambda(['date','ref'],Get(Var('ref')))
)

how could I return all the dates between that range but also for a certain user , using the key netlifyID ?..

I am also looking for a solution to get between two dates but only for a certain user/get a users documents between two dates. I was thinking of using GetCurrentIdentity() and return the documents for that user. Do you combine two indexes or just add it to the one index?