Group index results by days

Help
1

I am creating a query to return the orders made in the last 7 or 30 days of the month from the date of the query. I create the index that includes creationDate of each order. Through my knowledge in FQL, I managed to produce the following:

Paginate(
      Range(
        Match(Index("all_orders_creationDate_productsTotal_paymentTotal")),
        Now(),
        TimeSubtract(Now(), 7, "day")
      )
  )

This returns the orders from seven days ago:

{
  data: [
    [Time("2020-08-11T17:55:11.917339Z"), 1, 184.24],
    [Time("2020-08-10T19:57:01.671136Z"), 2, 231.95000000000002]
  ]
}

Is it possible to group these results by day? For example, group all orders placed seven days ago only (08/14/2020), six days (08/15/2020), five (08/16/2020), and so on until the day of the consultation? Or maybe I can do this outside of Fauna using just that result, in javascript for example?

My purpose is to create charts with this data, each day indicating the total number of sales and revenues.

2

Hi @mls,

just create a few documents like this:

 { ref: Ref(Collection("groupByDate"), "274472193340999170"),
  ts: 1598015931361400,
  data: { created: Date("2020-08-20"), val: 18 } }

and an index like this:

{ ref: Index("groupByDate_idx"),
  ts: 1598016410640000,
  active: true,
  serialized: true,
  name: 'groupByDate_idx',
  source: Collection("groupByDate"),
  values: [ { field: [ 'data', 'created' ] }, { field: [ 'ref' ] } ],
  partitions: 8 }

and with this code

Let(
  {
    resultset: Range(Match('groupByDate_idx'),ToDate(TimeSubtract(Now(),10,'days')),ToDate(Now())),
    distinct: Distinct(Map(Paginate(Var('resultset')),Lambda('x',Select([0],Var('x'))))),
    mapping: Map(Var('distinct'),Lambda('x',Range(Var('resultset'),Var('x'),Var('x'))))
    
  },
  Map(Var('mapping'),Lambda('x',Paginate(Var('x'))))
)

you can group by day and have a result like this:

{ data:
   [ { data:
        [ [ Date("2020-08-11"),
            Ref(Collection("groupByDate"), "274472193323173378") ],
          [ Date("2020-08-11"),
            Ref(Collection("groupByDate"), "274472193347290626") ] ] },
     { data:
        [ [ Date("2020-08-12"),
            Ref(Collection("groupByDate"), "274472193317929474") ],
          [ Date("2020-08-12"),
            Ref(Collection("groupByDate"), "274472193352532482") ] ] },
     { data:
        [ [ Date("2020-08-13"),
            Ref(Collection("groupByDate"), "274472193323172354") ],
          [ Date("2020-08-13"),
            Ref(Collection("groupByDate"), "274472193325270530") ] ] },
     { data:
        [ [ Date("2020-08-14"),
            Ref(Collection("groupByDate"), "274472193318978050") ],
          [ Date("2020-08-14"),
            Ref(Collection("groupByDate"), "274472193331560962") ],
          [ Date("2020-08-14"),
            Ref(Collection("groupByDate"), "274472193333658114") ],
          [ Date("2020-08-14"),
            Ref(Collection("groupByDate"), "274472193340998146") ],
          [ Date("2020-08-14"),
            Ref(Collection("groupByDate"), "274472193343096322") ],
          [ Date("2020-08-14"),
            Ref(Collection("groupByDate"), "274472193344144898") ],
          [ Date("2020-08-14"),
            Ref(Collection("groupByDate"), "274472193358823938") ],
          [ Date("2020-08-14"),
            Ref(Collection("groupByDate"), "274472193359873538") ] ] },
     { data:
        [ [ Date("2020-08-15"),
            Ref(Collection("groupByDate"), "274472193321076226") ],
          [ Date("2020-08-15"),
            Ref(Collection("groupByDate"), "274472193322123778") ],
          [ Date("2020-08-15"),
            Ref(Collection("groupByDate"), "274472193324220930") ],
          [ Date("2020-08-15"),
            Ref(Collection("groupByDate"), "274472193344143874") ],
          [ Date("2020-08-15"),
            Ref(Collection("groupByDate"), "274472193354630658") ],
          [ Date("2020-08-15"),
            Ref(Collection("groupByDate"), "274472193357775362") ],
          [ Date("2020-08-15"),
            Ref(Collection("groupByDate"), "274472193357776386") ],
          [ Date("2020-08-15"),
            Ref(Collection("groupByDate"), "274472193359872514") ] ] },
     { data:
        [ [ Date("2020-08-16"),
            Ref(Collection("groupByDate"), "274472193314783746") ],
          [ Date("2020-08-16"),
            Ref(Collection("groupByDate"), "274472193324221954") ],
          [ Date("2020-08-16"),
            Ref(Collection("groupByDate"), "274472193335755266") ],
          [ Date("2020-08-16"),
            Ref(Collection("groupByDate"), "274472193337852418") ],
          [ Date("2020-08-16"),
            Ref(Collection("groupByDate"), "274472193343095298") ],
          [ Date("2020-08-16"),
            Ref(Collection("groupByDate"), "274472193349387778") ],
          [ Date("2020-08-16"),
            Ref(Collection("groupByDate"), "274472193350436354") ],
          [ Date("2020-08-16"),
            Ref(Collection("groupByDate"), "274472193356726786") ],
          [ Date("2020-08-16"),
            Ref(Collection("groupByDate"), "274472193360921090") ] ] },
     { data:
        [ [ Date("2020-08-17"),
            Ref(Collection("groupByDate"), "274472193314784770") ],
          [ Date("2020-08-17"),
            Ref(Collection("groupByDate"), "274472193315832322") ],
          [ Date("2020-08-17"),
            Ref(Collection("groupByDate"), "274472193326318082") ],
          [ Date("2020-08-17"),
            Ref(Collection("groupByDate"), "274472193346241026") ],
          [ Date("2020-08-17"),
            Ref(Collection("groupByDate"), "274472193346242050") ],
          [ Date("2020-08-17"),
            Ref(Collection("groupByDate"), "274472193349386754") ],
          [ Date("2020-08-17"),
            Ref(Collection("groupByDate"), "274472193361969666") ] ] },
     { data:
        [ [ Date("2020-08-18"),
            Ref(Collection("groupByDate"), "274472193316880898") ],
          [ Date("2020-08-18"),
            Ref(Collection("groupByDate"), "274472193316881922") ],
          [ Date("2020-08-18"),
            Ref(Collection("groupByDate"), "274472193322124802") ],
          [ Date("2020-08-18"),
            Ref(Collection("groupByDate"), "274472193325269506") ],
          [ Date("2020-08-18"),
            Ref(Collection("groupByDate"), "274472193347289602") ],
          [ Date("2020-08-18"),
            Ref(Collection("groupByDate"), "274472193350435330") ] ] },
     { data:
        [ [ Date("2020-08-19"),
            Ref(Collection("groupByDate"), "274472193321075202") ],
          [ Date("2020-08-19"),
            Ref(Collection("groupByDate"), "274472193329463810") ],
          [ Date("2020-08-19"),
            Ref(Collection("groupByDate"), "274472193353581058") ],
          [ Date("2020-08-19"),
            Ref(Collection("groupByDate"), "274472193353582082") ],
          [ Date("2020-08-19"),
            Ref(Collection("groupByDate"), "274472193354629634") ],
          [ Date("2020-08-19"),
            Ref(Collection("groupByDate"), "274472193355678210") ],
          [ Date("2020-08-19"),
            Ref(Collection("groupByDate"), "274472193356727810") ],
          [ Date("2020-08-19"),
            Ref(Collection("groupByDate"), "274472193364066818") ] ] },
     { data:
        [ [ Date("2020-08-20"),
            Ref(Collection("groupByDate"), "274472193340999170") ] ] } ] }

You can modify the code/index as per your needs.
Hope this help.

Luigi

1 Like