Search a date range
Solution
The solution has several steps:
-
Create some documents to search
try { Value result = await client.Query( Map( Arr( Arr("Walk the dog", true, "2022-01-10"), Arr("Feed the cat", true, "2022-01-12"), Arr("Wash the car", false, "2022-01-26") ), Lambda( Arr("title", "completed", "date"), Create( Collection("Todo"), Obj("data", Obj( "title", Var("title"), "completed", Var("completed"), "completedDate", Date(Var("date")) ) ) ) ) ) ); Console.WriteLine(result); } catch (Exception e) { Console.WriteLine($"ERROR: {e.Message}"); }Arr(ObjectV(ref: RefV(id = "325694666525639168", collection = RefV(id = "Todo", collection = RefV(id = "collections"))),ts: LongV(1646865488470000),data: ObjectV(title: StringV(Walk the dog),completed: BooleanV(True),completedDate: FaunaDate(1/10/2022 12:00:00 AM))), ObjectV(ref: RefV(id = "325694666527736320", collection = RefV(id = "Todo", collection = RefV(id = "collections"))),ts: LongV(1646865488470000),data: ObjectV(title: StringV(Feed the cat),completed: BooleanV(True),completedDate: FaunaDate(1/12/2022 12:00:00 AM))), ObjectV(ref: RefV(id = "325694666529833472", collection = RefV(id = "Todo", collection = RefV(id = "collections"))),ts: LongV(1646865488470000),data: ObjectV(title: StringV(Wash the car),completed: BooleanV(False),completedDate: FaunaDate(1/26/2022 12:00:00 AM))))res, err := client.Query( f.Map( f.Arr{ f.Arr{"Walk the dog", true, "2022-01-10"}, f.Arr{"Feed the cat", true, "2022-01-12"}, f.Arr{"Wash the car", false, "2022-01-26"}, }, f.Lambda( f.Arr{"title", "completed", "date"}, f.Create( f.Collection("Todo"), f.Obj{ "data": f.Obj{ "title": f.Var("title"), "completed": f.Var("completed"), "completedDate": f.Date(f.Var("date")), }, }, ), ), ), ) if err != nil { fmt.Fprintln(os.Stderr, err) } else { fmt.Println(res) }[map[data:map[completed:true completedDate:{0 63777369600 <nil>} title:Walk the dog] ref:{325695214778843648 0xc000178060 0xc000178060 <nil>} ts:1646866011320000] map[data:map[completed:true completedDate:{0 63777542400 <nil>} title:Feed the cat] ref:{325695214781989376 0xc000178270 0xc000178270 <nil>} ts:1646866011320000] map[data:map[completed:false completedDate:{0 63778752000 <nil>} title:Wash the car] ref:{325695214784086528 0xc000178480 0xc000178480 <nil>} ts:1646866011320000]]System.out.println( client.query( Map( Arr( Arr(Value("Walk the dog"), Value(true), Value("2022-01-10")), Arr(Value("Feed the cat"), Value(true), Value("2022-01-12")), Arr(Value("Wash the car"), Value(false), Value("2022-01-26")) ), Lambda( Arr(Value("title"), Value("completed"), Value("date")), Create( Collection("Todo"), Obj( "data", Obj( "title", Var("title"), "completed", Var("completed"), "completedDate", Date(Var("date")) ) ) ) ) ) ).get());[{ref: ref(id = "325695290959987200", collection = ref(id = "Todo", collection = ref(id = "collections"))), ts: 1646866083970000, data: {title: "Walk the dog", completed: true, completedDate: 2022-01-10}}, {ref: ref(id = "325695290962084352", collection = ref(id = "Todo", collection = ref(id = "collections"))), ts: 1646866083970000, data: {title: "Feed the cat", completed: true, completedDate: 2022-01-12}}, {ref: ref(id = "325695290963132928", collection = ref(id = "Todo", collection = ref(id = "collections"))), ts: 1646866083970000, data: {title: "Wash the car", completed: false, completedDate: 2022-01-26}}]client.query( q.Map( [ ['Walk the dog', true, '2022-01-10'], ['Feed the cat', true, '2022-01-12'], ['Wash the car', false, '2022-01-26'], ], q.Lambda( ['title', 'completed', 'date'], q.Create( q.Collection('Todo'), { data: { title: q.Var('title'), completed: q.Var('completed'), completedDate: q.Date(q.Var('date')), }, } ) ) ) ) .then((ret) => console.log(ret)) .catch((err) => console.error( 'Error: [%s] %s: %s', err.name, err.message, err.errors()[0].description, ))[ { ref: Ref(Collection("Todo"), "325693420482855424"), ts: 1646864300150000, data: { title: 'Walk the dog', completed: true, completedDate: Date("2022-01-10") } }, { ref: Ref(Collection("Todo"), "325693420484952576"), ts: 1646864300150000, data: { title: 'Feed the cat', completed: true, completedDate: Date("2022-01-12") } }, { ref: Ref(Collection("Todo"), "325693420487049728"), ts: 1646864300150000, data: { title: 'Wash the car', completed: false, completedDate: Date("2022-01-26") } } ]result = client.query( q.map_( q.lambda_( ["title", "completed", "date"], q.create( q.collection("Todo"), { "data": { "title": q.var("title"), "completed": q.var("completed"), "completedDate": q.date(q.var("date")), } } ) ), [ ['Walk the dog', True, '2022-01-10'], ['Feed the cat', True, '2022-01-12'], ['Wash the car', False, '2022-01-26'], ] ) ) print(result)[{'ref': Ref(id=325695130244743680, collection=Ref(id=Todo, collection=Ref(id=collections))), 'ts': 1646865930700000, 'data': {'title': 'Walk the dog', 'completed': True, 'completedDate': datetime.date(2022, 1, 10)}}, {'ref': Ref(id=325695130246840832, collection=Ref(id=Todo, collection=Ref(id=collections))), 'ts': 1646865930700000, 'data': {'title': 'Feed the cat', 'completed': True, 'completedDate': datetime.date(2022, 1, 12)}}, {'ref': Ref(id=325695130248937984, collection=Ref(id=Todo, collection=Ref(id=collections))), 'ts': 1646865930700000, 'data': {'title': 'Wash the car', 'completed': False, 'completedDate': datetime.date(2022, 1, 26)}}]Map( [ ["Walk the dog", true, "2022-01-10"], ["Feed the cat", true, "2022-01-12"], ["Wash the car", false, "2022-01-26"], ], Lambda( ["title", "completed", "date"], Create( Collection("Todo"), { data: { title: Var("title"), completed: Var("completed"), completedDate: Date(Var("date")), } } ) ) )[ { ref: Ref(Collection("Todo"), "325694738894160384"), ts: 1646865557490000, data: { title: 'Walk the dog', completed: true, completedDate: Date("2022-01-10") } }, { ref: Ref(Collection("Todo"), "325694738896257536"), ts: 1646865557490000, data: { title: 'Feed the cat', completed: true, completedDate: Date("2022-01-12") } }, { ref: Ref(Collection("Todo"), "325694738898354688"), ts: 1646865557490000, data: { title: 'Wash the car', completed: false, completedDate: Date("2022-01-26") } } ]Query metrics:-
bytesIn: 346
-
bytesOut: 717
-
computeOps: 1
-
readOps: 0
-
writeOps: 3
-
readBytes: 0
-
writeBytes: 723
-
queryTime: 45ms
-
retries: 0
-
-
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
-
-
Create a UDF
try { Value result = await client.Query( CreateFunction( Obj( "name", "todosByDateRange", "body", Query( Lambda( Arr("fromDate", "toDate"), Map( Select( Arr("data"), Paginate( Range( Match(Index("todos_by_completed_date")), Date(Var("fromDate")), Date(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(1646866426120000),name: StringV(todosByDateRange),body: QueryV(System.Collections.Generic.Dictionary`2[System.String,FaunaDB.Query.Expr]))result, err := client.Query( f.CreateFunction( f.Obj{ "name": "todosByDateRange", "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_by_completed_date")), f.Date(f.Var("fromDate")), f.Date(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 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 100 97 116 101 34 58 123 34 118 97 114 34 58 34 102 114 111 109 68 97 116 101 34 125 125 44 34 116 111 34 58 123 34 100 97 116 101 34 58 123 34 118 97 114 34 58 34 116 111 68 97 116 101 34 125 125 125 125 125 125 125]} name:todosByDateRange ref:{todosByDateRange 0xc000120180 0xc000120180 <nil>} ts:1646866446050000]System.out.println( client.query( CreateFunction( Obj( "name", Value("todosByDateRange"), "body", Query( Lambda( Arr(Value("fromDate"), Value("toDate")), Map( Select( Arr(Value("data")), Paginate( Range( Match( Index("todos_by_completed_date") ), Date(Var("fromDate")), Date(Var("toDate")) ) ) ), Lambda( Arr(Value("date"), Value("ref")), Get(Var("ref")) ) ) ) ) ) ) ).get());{ref: ref(id = "todosByDateRange", collection = ref(id = "functions")), ts: 1646866350100000, name: "todosByDateRange", body: QueryV({api_version=4, lambda=[fromDate, toDate], expr={map={lambda=[date, ref], expr={get={var=ref}}}, collection={select=[data], from={paginate={range={match={index=todos_by_completed_date}}, from={date={var=fromDate}}, to={date={var=toDate}}}}}}})}client.query( q.CreateFunction({ name: 'todosByDateRange', body: q.Query( q.Lambda( ['fromDate', 'toDate'], q.Map( q.Select( ['data'], q.Paginate( q.Range( q.Match(q.Index('todos_by_completed_date')), q.Date(q.Var('fromDate')), q.Date(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: 1646864300490000, name: 'todosByDateRange', body: Query(Lambda(["fromDate", "toDate"], Map(Select(["data"], Paginate(Range(Match(Index("todos_by_completed_date")), Date(Var("fromDate")), Date(Var("toDate"))))), Lambda(["date", "ref"], Get(Var("ref")))))) }result = client.query( q.create_function({ 'name': '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_by_completed_date")), q.date(q.var("fromDate")), q.date(q.var("toDate")) ) ) ) ) ) ) }) ) print(result){'ref': Ref(id=todosByDateRange, collection=Ref(id=functions)), 'ts': 1646866495410000, '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_by_completed_date'}}, 'from': {'date': {'var': 'fromDate'}}, 'to': {'date': {'var': 'toDate'}}}}}}})}CreateFunction({ name: "todosByDateRange", body: Query( Lambda( ["fromDate", "toDate"], Map( Select( ["data"], Paginate( Range( Match(Index("todos_by_completed_date")), Date(Var("fromDate")), Date(Var("toDate")) ) ) ), Lambda(["date", "ref"], Get(Var("ref"))) ) ) ) }){ ref: Function("todosByDateRange"), ts: 1646866626110000, name: 'todosByDateRange', body: Query(Lambda(["fromDate", "toDate"], Map(Select(["data"], Paginate(Range(Match(Index("todos_by_completed_date")), Date(Var("fromDate")), Date(Var("toDate"))))), Lambda(["date", "ref"], Get(Var("ref")))))) }Query metrics:-
bytesIn: 364
-
bytesOut: 469
-
computeOps: 1
-
readOps: 0
-
writeOps: 1
-
readBytes: 29
-
writeBytes: 567
-
queryTime: 40ms
-
retries: 0
The UDF makes it easy to re-use the query.
-
-
Call the UDF
try { Value result = await client.Query( Call( Function("todosByDateRange"), Arr("2022-01-01", "2022-01-15") ) ); Console.WriteLine(result); } catch (Exception e) { Console.WriteLine($"ERROR: {e.Message}"); }Arr(ObjectV(ref: RefV(id = "325696022305047040", collection = RefV(id = "Todo", collection = RefV(id = "collections"))),ts: LongV(1646866781440000),data: ObjectV(title: StringV(Walk the dog),completed: BooleanV(True),completedDate: FaunaDate(1/10/2022 12:00:00 AM))), ObjectV(ref: RefV(id = "325696022307144192", collection = RefV(id = "Todo", collection = RefV(id = "collections"))),ts: LongV(1646866781440000),data: ObjectV(title: StringV(Feed the cat),completed: BooleanV(True),completedDate: FaunaDate(1/12/2022 12:00:00 AM))))res, err := client.Query( f.Call( f.Function("todosByDateRange"), f.Arr{"2022-01-01", "2022-01-15"}, )) if err != nil { fmt.Fprintln(os.Stderr, err) } else { fmt.Println(res) }[map[data:map[completed:true completedDate:{0 63777369600 <nil>} title:Walk the dog] ref:{325696054352675328 0xc000180570 0xc000180570 <nil>} ts:1646866812000000] map[data:map[completed:true completedDate:{0 63777542400 <nil>} title:Feed the cat] ref:{325696054355821056 0xc000180780 0xc000180780 <nil>} ts:1646866812000000]]System.out.println( client.query( Call( Function("todosByDateRange"), Arr( Value("2022-01-01"), Value("2022-01-15") ) ) ).get());[{ref: ref(id = "325696075333632512", collection = ref(id = "Todo", collection = ref(id = "collections"))), ts: 1646866832010000, data: {title: "Walk the dog", completed: true, completedDate: 2022-01-10}}, {ref: ref(id = "325696075335729664", collection = ref(id = "Todo", collection = ref(id = "collections"))), ts: 1646866832010000, data: {title: "Feed the cat", completed: true, completedDate: 2022-01-12}}]client.query( q.Call('todosByDateRange', '2022-01-01', '2022-01-15') ) .then((ret) => console.log(ret)) .catch((err) => console.error( 'Error: [%s] %s: %s', err.name, err.message, err.errors()[0].description, ))[ { ref: Ref(Collection("Todo"), "325693420482855424"), ts: 1646864300150000, data: { title: 'Walk the dog', completed: true, completedDate: Date("2022-01-10") } }, { ref: Ref(Collection("Todo"), "325693420484952576"), ts: 1646864300150000, data: { title: 'Feed the cat', completed: true, completedDate: Date("2022-01-12") } } ]try: result = client.query( q.call("todosByDateRange", "2022-01-01", "2022-01-15") ) print(result) except: print("Error: ", sys.exc_info()[0], sys.exc_info()[1])[{'ref': Ref(id=325696105392112128, collection=Ref(id=Todo, collection=Ref(id=collections))), 'ts': 1646866860680000, 'data': {'title': 'Walk the dog', 'completed': True, 'completedDate': datetime.date(2022, 1, 10)}}, {'ref': Ref(id=325696105394209280, collection=Ref(id=Todo, collection=Ref(id=collections))), 'ts': 1646866860680000, 'data': {'title': 'Feed the cat', 'completed': True, 'completedDate': datetime.date(2022, 1, 12)}}][ { ref: Ref(Collection("Todo"), "325696243734938112"), ts: 1646866992610000, data: { title: 'Walk the dog', completed: true, completedDate: Date("2022-01-10") } }, { ref: Ref(Collection("Todo"), "325696243737035264"), ts: 1646866992610000, data: { title: 'Feed the cat', completed: true, completedDate: Date("2022-01-12") } } ]Query metrics:-
bytesIn: 67
-
bytesOut: 482
-
computeOps: 1
-
readOps: 10
-
writeOps: 0
-
readBytes: 462
-
writeBytes: 0
-
queryTime: 35ms
-
retries: 0
-
Discussion
The UDF todosByDateRange uses the Range function to find
documents which have a completedDate field which is within a given
range. Range takes three parameters: a set, a starting value, and an
ending value. Range is inclusive, so it matches up to and including
the ending value.
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!