Upper and lower bound index query expressions with different field names
I'm getting a Typescript error, so I assume it isn't possible, but I thought I'd ask. Is it possible to use different field names for the upper and lower bound expressions in an index query (assuming it still follows the rule about keeping the same order as the fields in the index)? Or will it/could it be?
3 Replies
Hi! This is not possible, and it likely will never be possible. Think of indexes like an ordering of your documents, lexicographically. So an index on [notebook, endDate, startDate] puts all documents in order by notebook, then if they have the same notebook order by endDate, and if they also have the same endDate order by startDate (and the final tiebreaker is _creationTime). When you query from an index, you give it a contiguous range of the sorted index, so endDate>=start is contiguous but startDate<start is not. For your case I can't think of an ordering of documents that would make the startDate<start<=endDate set of documents contiguous. I would suggest you only do one condition in the index, and put the other condition in a
.filterMakes sense, thanks @lee !
Good luck, and feel free to ask any more questions 🙂 . Data model design can be tricky, and it really feels like there should be an efficient way to write a query like you described