Search a date range

Problem

You want to find documents which contain a date within a specified range.

Solution

You can search for query results in a range of dates using an index and a user-defined function.

The solution has several steps:

Update your GraphQL schema

This example needs the following types:
```
type Todo {
  title: String!
  completed: Boolean
  completedDate: Date!
}

type Query {
  todosByDateRange(fromDate: Date!, toDate: Date!): [Todo!] @resolver(name: "todosByDateRange")
}
```
This involves editing the GraphQL schema definition, wherever you have stored it, and then uploading the new schema in the Fauna Dashboard.

When you import the above schema, the GraphQL API automatically creates a collection named Todo and a UDF named todosByDateRange.

For more information about importing GraphQL schemas, see the GraphQL quick start.

Create some documents to search

mutation {
  Todo1: createTodo( data:{
    title: "Walk the dog"
    completed: true
    completedDate: "2022-01-10"
  }) {
    _id
  },
  Todo2: createTodo( data:{
    title: "Feed the cat"
    completed: true
    completedDate: "2022-01-12"
  }) {
    _id
  },
  Todo3: createTodo( data:{
    title: "Wash the car"
    completed: false
    completedDate: "2022-01-26"
  }) {
    _id
  }
}

Create an index to search by date range

try
{
    Value result = await client.Query(
        CreateIndex(
            Obj(
                "name", "todos_by_completed_date",
                "source", Collection("Todo"),
                "values", Arr(
                    Obj("field", Arr("data", "completedDate")),
                    Obj("field", "ref")
                )
            )
        )
    );
    Console.WriteLine(result);
}
catch (Exception e)
{
    Console.WriteLine($"ERROR: {e.Message}");
}

ObjectV(ref: RefV(id = "todos_by_completed_date", collection = RefV(id = "indexes")),ts: LongV(1643996142270000),active: BooleanV(True),serialized: BooleanV(True),name: StringV(todos_by_completed_date),source: RefV(id = "Todo", collection = RefV(id = "collections")),values: Arr(ObjectV(field: Arr(StringV(data), StringV(completedDate))), ObjectV(field: StringV(ref))),partitions: LongV(8))

res, err := client.Query(
	f.CreateIndex(
		f.Obj{
			"name": "todos_by_completed_date",
			"source": f.Collection("Todo"),
			"values": f.Arr{
				f.Obj{"field": f.Arr{"data", "completedDate"}},
				f.Obj{"field": "ref" },
			},
		},
	))

if err != nil {
	fmt.Fprintln(os.Stderr, err)
} else {
	fmt.Println(res)
}

map[active:true name:todos_by_completed_date partitions:8 ref:{todos_by_completed_date 0xc0001844b0 0xc0001844b0 <nil>} serialized:true source:{Todo 0xc0001845a0 0xc0001845a0 <nil>} ts:1629916461970000 values:[map[field:[data completedDate]] map[field:ref]]]

System.out.println(
    client.query(
        CreateIndex(
            Obj(
                "name", Value("todos_by_completed_date"),
                "source", Collection("Todo"),
                "values", Arr(
                    Obj("field", Arr(Value("data"), Value("completedDate"))),
                    Obj("field", Value("ref"))
                )
            )
        )
    ).get());

{ref: ref(id = "todos_by_completed_date", collection = ref(id = "indexes")), ts: 1629918327510000, active: true, serialized: true, name: "todos_by_completed_date", source: ref(id = "Todo", collection = ref(id = "collections")), values: [{field: ["data", "completedDate"]}, {field: "ref"}], partitions: 8}

client.query(
  q.CreateIndex({
    name: 'todos_by_completed_date',
    source: q.Collection('Todo'),
    values: [
      { field: ['data', 'completedDate'] },
      { field: ['ref'] },
    ],
  })
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))

{
  ref: Index("todos_by_completed_date"),
  ts: 1643847446290000,
  active: true,
  serialized: true,
  name: 'todos_by_completed_date',
  source: Collection("Todo"),
  values: [ { field: [ 'data', 'completedDate' ] }, { field: [ 'ref' ] } ],
  partitions: 8
}

result = client.query(
  q.create_index({
    "name": "todos_by_completed_date",
    "source": q.collection("Todo"),
    "values": [
      { "field": ["data", "completedDate"] },
      { "field": "ref" },
    ],
  })
)
print(result)

{'ref': Ref(id=todos_by_completed_date, collection=Ref(id=indexes)), 'ts': 1643927420670000, 'active': True, 'serialized': True, 'name': 'todos_by_completed_date', 'source': Ref(id=Todo, collection=Ref(id=collections)), 'values': [{'field': ['data', 'completedDate']}, {'field': 'ref'}], 'partitions': 8}

CreateIndex({
  name: "todos_by_completed_date",
  source: Collection("Todo"),
  values: [
    { field: ["data", "completedDate"] },
    { field: ["ref"] }
  ]
})

{
  ref: Index("todos_by_completed_date"),
  ts: 1643836096660000,
  active: true,
  serialized: true,
  name: 'todos_by_completed_date',
  source: Collection("Todo"),
  values: [ { field: [ 'data', 'completedDate' ] }, { field: [ 'ref' ] } ],
  partitions: 8
}

Query metrics:

bytesIn: 179
bytesOut: 342
computeOps: 1
readOps: 0
writeOps: 4
readBytes: 1,691
writeBytes: 922
queryTime: 37ms
retries: 0

Update the sub function todosByDateRange

Our schema defined a query called todosByDateRange, which, due to the @resolver directive directive, caused the GraphQL API to create a UDF with that name. However, the GraphQL API doesn’t know what should happen. We need to update the function to provide the appropriate operations:

try
{
    Value result = await client.Query(
        Update(
            Function("todosByDateRange"),
            Obj(
                "body", Query(
                    Lambda(
                        Arr("fromDate", "toDate"),
                        Map(
                            Select(
                                Arr("data"),
                                Paginate(
                                    Range(
                                        Match(Index("todos_sorted_by_completed_date")),
                                        Var("fromDate"),
                                        Var("toDate")
                                    )
                                )
                            ),
                            Lambda(Arr("date", "ref"), Get(Var("ref")))
                        )
                    )
                )
            )
        )
    );
    Console.WriteLine(result);
}
catch (Exception e)
{
    Console.WriteLine($"ERROR: {e.Message}");
}

ObjectV(ref: RefV(id = "todosByDateRange", collection = RefV(id = "functions")),ts: LongV(1646418849590000),name: StringV(todosByDateRange),body: QueryV(System.Collections.Generic.Dictionary`2[System.String,FaunaDB.Query.Expr]))

result, err := client.Query(
	f.Update(
		f.Function("todosByDateRange"),
		f.Obj{
			"body": f.Query(
				f.Lambda(
					f.Arr{"fromDate", "toDate"},
					f.Map(
						f.Select(
							f.Arr{"data"},
							f.Paginate(
							  f.Range(
								f.Match(f.Index("todos_sorted_by_completed_date")),
								f.Var("fromDate"),
								f.Var("toDate"),
							  ),
							),
						  ),
						f.Lambda(f.Arr{"date", "ref"}, f.Get(f.Var("ref"))))))}))

if err != nil {
	fmt.Fprintln(os.Stderr, err)
} else {
	fmt.Println(result)
}

map[body:{[123 34 97 112 105 95 118 101 114 115 105 111 110 34 58 34 52 34 44 34 108 97 109 98 100 97 34 58 91 34 102 114 111 109 68 97 116 101 34 44 34 116 111 68 97 116 101 34 93 44 34 101 120 112 114 34 58 123 34 109 97 112 34 58 123 34 108 97 109 98 100 97 34 58 91 34 100 97 116 101 34 44 34 114 101 102 34 93 44 34 101 120 112 114 34 58 123 34 103 101 116 34 58 123 34 118 97 114 34 58 34 114 101 102 34 125 125 125 44 34 99 111 108 108 101 99 116 105 111 110 34 58 123 34 115 101 108 101 99 116 34 58 91 34 100 97 116 97 34 93 44 34 102 114 111 109 34 58 123 34 112 97 103 105 110 97 116 101 34 58 123 34 114 97 110 103 101 34 58 123 34 109 97 116 99 104 34 58 123 34 105 110 100 101 120 34 58 34 116 111 100 111 115 95 115 111 114 116 101 100 95 98 121 95 99 111 109 112 108 101 116 101 100 95 100 97 116 101 34 125 125 44 34 102 114 111 109 34 58 123 34 118 97 114 34 58 34 102 114 111 109 68 97 116 101 34 125 44 34 116 111 34 58 123 34 118 97 114 34 58 34 116 111 68 97 116 101 34 125 125 125 125 125 125]} name:todosByDateRange ref:{todosByDateRange 0xc0001844b0 0xc0001844b0 <nil>} ts:1646418856170000]

System.out.println(
    client.query(
        Update(
            Function("todosByDateRange"),
            Obj(
                "body", Query(
                     Lambda(
                         Arr(Value("fromDate"), Value("toDate")),
                         Map(
                             Select(
                                 Arr(Value("data")),
                                 Paginate(
                                     Range(
                                         Match(Index(Value("todos_sorted_by_completed_date"))),
                                         Var(Value("fromDate")),
                                         Var(Value("toDate"))
                                     )
                                 )
                             ),
                             Lambda(Arr(Value("date"), Value("ref")), Get(Var(Value("ref"))))
                         )
                     )  
                )
            )
        )
    ).get());

{ref: ref(id = "todosByDateRange", collection = ref(id = "functions")), ts: 1644280670240000, name: "todosByDateRange", body: QueryV({api_version=4, lambda=[fromDate, toDate], expr={map={lambda=row, expr={get={select=[1], from={var=row}}}}, collection={intersection=[{select=[data], from={paginate={match={index=todos_sorted_by_completed_date}}, after={var=fromDate}, size=100000}}, {select=[data], from={paginate={match={index=todos_sorted_by_completed_date}}, before={var=toDate}, size=100000}}]}}})}

client.query(
  q.Update(
    q.Function('todosByDateRange'),
    {
      body: q.Query(
        q.Lambda(
          ['fromDate', 'toDate'],
          q.Map(
            q.Select(
              ['data'],
              q.Paginate(
                q.Range(
                  q.Match(q.Index('todos_sorted_by_completed_date')),
                  q.Var('fromDate'),
                  q.Var('toDate')
                )
              )
            ),
            q.Lambda(['date', 'ref'], q.Get(q.Var('ref')))
          )
        )
      ),
    }
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))

{
  ref: Function("todosByDateRange"),
  ts: 1646418888460000,
  name: 'todosByDateRange',
  body: Query(Lambda(["fromDate", "toDate"], Map(Select(["data"], Paginate(Range(Match(Index("todos_sorted_by_completed_date")), Var("fromDate"), Var("toDate")))), Lambda(["date", "ref"], Get(Var("ref"))))))
}

result = client.query(
  q.update(
    q.function('todosByDateRange'),
    {
      'body': q.query(
        q.lambda_(
          ['fromDate', 'toDate'],
          q.map_(
            q.lambda_(["date", "ref"], q.get(q.var("ref"))),
            q.select(
              ["data"],
              q.paginate(
                q.range(
                  q.match(q.index("todos_sorted_by_completed_date")),
                  q.var("fromDate"),
                  q.var("toDate")
                )
              )
            )          
          )
        )
      )
    }
  )
)
print(result)

{'ref': Ref(id=todosByDateRange, collection=Ref(id=functions)), 'ts': 1646418892280000, 'name': 'todosByDateRange', 'body': Query({'api_version': '4', 'lambda': ['fromDate', 'toDate'], 'expr': {'map': {'lambda': ['date', 'ref'], 'expr': {'get': {'var': 'ref'}}}, 'collection': {'select': ['data'], 'from': {'paginate': {'range': {'match': {'index': 'todos_sorted_by_completed_date'}}, 'from': {'var': 'fromDate'}, 'to': {'var': 'toDate'}}}}}})}

Update(
  Function("todosByDateRange"),
  {
    body: Query(
      Lambda(
      ["fromDate", "toDate"],
        Map(
          Select(
            ["data"],
            Paginate(
              Range(
                Match(Index("todos_sorted_by_completed_date")),
                Var("fromDate"),
                Var("toDate")
              )
            )
          ),
          Lambda(["date", "ref"], Get(Var("ref")))
        )
      )
    )
  }
)

{
  ref: Function("todosByDateRange"),
  ts: 1646419021630000,
  name: 'todosByDateRange',
  body: Query(Lambda(["fromDate", "toDate"], Map(Select(["data"], Paginate(Range(Match(Index("todos_sorted_by_completed_date")), Var("fromDate"), Var("toDate")))), Lambda(["date", "ref"], Get(Var("ref"))))))
}

Query metrics:

bytesIn: 359
bytesOut: 458
computeOps: 1
readOps: 0
writeOps: 1
readBytes: 289
writeBytes: 515
queryTime: 21ms
retries: 0

Call the resolver

query getTodos {
  todosByDateRange(
    fromDate: "2022-01-01",
    toDate: "2022-01-15"
  ) {
    title
    completed
    completedDate
  }
}

The query generates the following results:

{
  "data": {
    "todosByDateRange": [
      {
        "title": "Walk the dog",
        "completed": true,
        "completedDate": "2022-01-10"
      },
      {
        "title": "Feed the cat",
        "completed": true,
        "completedDate": "2022-01-12"
      }
    ]
  }
}

More information

Is this article helpful?

Tell Fauna how the article can be improved:
Visit Fauna's forums or email docs@fauna.com

Thank you for your feedback!

Search a date range

Problem

Solution

More information

Keyboard shortcuts

While reading

While searching

URL fragments