FQL v4 will be decommissioned on June 30, 2025. Ensure that you complete your migration from FQL v4 to FQL v10 by that date. Fauna accounts created after August 21, 2024 must use FQL v10. These accounts will not be able to run FQL v4 queries or access the v4 Dashboard. For more details, see the v4 EOL announcement and migration guide. Contact support@fauna.com with any questions. |
Search a date range
Solution
The solution has several steps:
-
Create some documents to search
[ { 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") } } ]
[{'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[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]]
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))))
[{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}}]
[ { 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") } } ]
-
Create an index to search by date range
{ 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 }
{'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}
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]]]
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))
{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}
{ 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 }
-
Create a UDF
{ 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")))))) }
{'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'}}}}}}})}
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]
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]))
{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}}}}}}})}
{ 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")))))) }
The UDF makes it easy to re-use the query.
-
Call the UDF
[ { 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(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)}}]
[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]]
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))))
[{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}}]
[ { 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") } } ]
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!