allen•12mo ago

undefined < Number === true?

I have an optional expiresAt field in my schema that holds an expiration date for a record. If its undefined, it should never expire. I have an index on this field as I have a cron job running every minute to see what should be expired. That query looks like so:

db
      .query('requests')
      .withIndex('by_expiration', (q) =>
        q.eq('isExpired', false).lte('expiresAt', Date.now()),
      )

db
      .query('requests')
      .withIndex('by_expiration', (q) =>
        q.eq('isExpired', false).lte('expiresAt', Date.now()),
      )

However, this returns records that have an undefined value for the expiresAt field, requiring me to filter these in memory with:

.filter((q) => q.neq(q.field('expiresAt'), undefined))

.filter((q) => q.neq(q.field('expiresAt'), undefined))

Being this runs so frequently, its eating through my db bandwidth, retrieving these unwanted rows. Before I get cute with my schema to hack around this... 1) Is this the intended behavior of the query engine? It doesnt match javascript's behavior. 2) Is there a way to better structure or query my existing index to remove these results?

7 Replies

lee•12mo ago

hi! yes this is intended behavior, although i agree it's confusing. we've considered restricting .lte and friends to be bounded on the type of the input, so if you .lte with a number, it would only return numbers. there were some unintended consequences, so we decided against that plan, but we could pick it up if there is enough demand. for now you can structure your query to filter out undefined values by accounting for the fact that undefined < null < Number within convex's ordering scheme, and while you can't pass undefined to a comparator, you can pass null. so the query could look like q.eq('isExpired', false).gt('expiresAt', null as any).lte('expiresAt', Date.now())

allenOP•12mo ago

That makes sense. It seems like appending .gt('expiresAt', 0) works equally as well.

lee•12mo ago

oh yeah that's simpler 😄

allenOP•12mo ago

For some reason I had it in my head that only one comparison per indexed field was allowed I think its because the intellisense chaining on the query builder doesnt suggest anything after you've satisfied one condition per field indexed.

lee•12mo ago

you can only do two comparisons on the last field of the index, and we've got some complex typescript to make it work. not sure how well that plays with IDEs

allenOP•12mo ago

ah interesting. I'll have to keep that restriction in mind when defining indexes another thing that hasn't been clear to me is how .order applies its logic, as it doesnt take a field as an argument It seems its using _creationTime by default, but then some parts of the docs seem to insinuate that it will utilized indexed field when combined with withIndex, but not sure WHICH indexed field if there are multiple. Is it also the last one?

jamwt•12mo ago

(1) if you don't specify an index, you are still using an index, and the index is the default index, which is creation time so then the second part of the question (2) yes, .order is always relative to an index then and if there are multiple fields, that's equivalent to (A, A), (A, B), (A, C) (B, A), (B, B) etc. it's basically like sorting the tuples basically, you're traversing a btree either forward or backward and the keys of the btree are tuples of all indexed field values this property is also why this statement of lee's is true: "you can only do two comparisons on the last field of the index". you can't specify two comparisons on earlier fields otherwise you'd be specifying possibly non-sequential ranges of the index

undefined < Number === true?

Did you find this page helpful?