RJ•2y ago

How to filter by indexed field and order by creation time?

Let's say I have a schema with a table like this:

dogs: defineTable({
  owner: s.id("people"),
}).index("by_owner", ["owner"]),

dogs: defineTable({
  owner: s.id("people"),
}).index("by_owner", ["owner"]),

And I want to write a query which returns for me a list of all of the dogs owned by a particular person, sorted with the newest dogs at the front (meaning sorted by _creationTime descending). How would I accomplish that? Both

db
  .query("dogs")
  .withIndex("by_owner", (q) =>
    q.eq("owner", ownerId))
  )
  .order("desc")

db
  .query("dogs")
  .withIndex("by_owner", (q) =>
    q.eq("owner", ownerId))
  )
  .order("desc")

and

db
  .query("dogs")
  .withIndex("by_owner", (q) =>
    q.eq("owner", ownerId))
  )
  .order("asc")

db
  .query("dogs")
  .withIndex("by_owner", (q) =>
    q.eq("owner", ownerId))
  )
  .order("asc")

seem to return the same result, which makes me think, as perhaps stated in https://discord.com/channels/1019350475847499849/1097530963262717982, that order is sorting only ownerId.

13 Replies

ian•2y ago

That is strange, and would be a bug I believe. And just to confirm, the “same result” is a result of length >= 2 where the creation times are different?

RJOP•2y ago

Sorry, what I said is not accurate! They do not return the same results. Let me test this some more to make sure I'm describing a real problem I'm having (or at least the actual problem I'm having) Perhaps I have a question then—what should I expect the sorting behavior to be in the above two scenarios?

ian•2y ago

The behavior should be sorting by owner, then _creationTime. So asc would be oldest to newest and desc would be newest to oldest, if they're all from the same owner

RJOP•2y ago

Ok got it, thank you @ian! How would I handle a scenario like this one? Given this schema:

dogs: defineTable({
  age: s.number(),
}).index("by_age", ["age"]),

dogs: defineTable({
  age: s.number(),
}).index("by_age", ["age"]),

Say I want to query for the oldest dogs inserted the least recently (so, sorting by age descending and then _creationTime ascending). Is there a way to do this without sorting in JS?

ian•2y ago

How about this:

// possibly tied for oldest
const oldestDog = await db.query('dogs').withIndex('by_age').order('desc').first();
const oldestAge = oldestDog.age;

const latestOldDog = await db.query('dogs').withIndex('by_age', q => q.eq('age', oldestAge)).first();

// possibly tied for oldest
const oldestDog = await db.query('dogs').withIndex('by_age').order('desc').first();
const oldestAge = oldestDog.age;

const latestOldDog = await db.query('dogs').withIndex('by_age', q => q.eq('age', oldestAge)).first();

if you wanted to do two optimized queries. An alternative would be to iterate over the results in JS, which might load more than you want:

const dogsOldestToYoungest = await db.query('dogs').withIndex('by_age').order('desc');
let latestOldDog;
for async (const dog of dogsOldestToYoungest) {
  if (!latestOldDog) {
    latestOldDog = dog;
    continue;
  }
  if (dog.age !== latestOldDog.age) {
    break;
  }
  latestOldDog = dog;
}

const dogsOldestToYoungest = await db.query('dogs').withIndex('by_age').order('desc');
let latestOldDog;
for async (const dog of dogsOldestToYoungest) {
  if (!latestOldDog) {
    latestOldDog = dog;
    continue;
  }
  if (dog.age !== latestOldDog.age) {
    break;
  }
  latestOldDog = dog;
}

This would be fetching a bunch of rows at once, but wouldn't fetch everything like sorting in JS would

RJOP•2y ago

The first doesn't work for the oldest dogs (plural) inserted the least recently, but the latter could be adjusted to I'd imagine? I guess I mainly wanted to know if it were possible to order by two different fields in different directions without resorting to async iterables, but it sounds like the answer is no (or not yet). Now that I understand better the situation here I don't think the current behavior is a problem for me. Thanks for your help @ian!

ian•2y ago

Ah, for all the dogs tied for oldest, I think you'd just change first to collect:

// possibly tied for oldest
const oldestDog = await db.query('dogs').withIndex('by_age').order('desc').first();
const oldestAge = oldestDog.age;

const oldDogs = await db.query('dogs').withIndex('by_age', q => q.eq('age', oldestAge)).collect();

// possibly tied for oldest
const oldestDog = await db.query('dogs').withIndex('by_age').order('desc').first();
const oldestAge = oldestDog.age;

const oldDogs = await db.query('dogs').withIndex('by_age', q => q.eq('age', oldestAge)).collect();

oh, but now I understand you just want the newest insert as a tiebreaker, not just the oldest

RJOP•2y ago

Yeah sorry, probably could have made that more clear!

ian•2y ago

yeah, I'm actually not sure about that. I assume we can't do it today. but would this syntax feel ergonomic, if we did support it? .withIndex('by_age', q => q.orderBy('age', 'desc').orderBy('_creationTime', 'asc'))

RJOP•2y ago

Yes, that's the sort of thing I was looking for!

presley•2y ago

I don't think that syntax will work since you need to have two indexes ordred differently to satisfy all permutations (if there were more fields, you would need more). We need to add the ability to define the index with the expected ordering, like if you define index that is descending by 'age' (and ascending by creationTime), you can use that index to satisfy your query. If you have queries that require different orderings, you might need to define more than one index.

RJOP•2y ago

I see, that makes sense I'm not sure how often this would come up, anyways—I think that it would be most common that I (or anyone) would want to order by exactly two fields, and one would probably be the _creationTime. On the other hand, I don't need to be able to sort using indexes (do I?) Point being that sorting in JS with async iterable is kind of annoying, and could be easier (even if not more performant) to be able to accomplish the same with the Convex querying JS API Which I suppose could do the sorting in JS using async iterable behind the scenes, if it wanted to

ian•2y ago

Also, if you're doing that kind of sorting, I'd recommend just grabbing a bunch of records and using sort, which would be much more ergonomic. You can have your queries be "paged" by age - fetch all dogs 0-5 and sort them however you want. I agree the async iterator stuff isn't fun.

oldDogs.sort((a, b) => b.age - a.age || a._creationTime - b._creationTime);

oldDogs.sort((a, b) => b.age - a.age || a._creationTime - b._creationTime);

I think would work? And this is assuming you even want to page the data. If there's <1k dogs, just .collect() and be done 🙂

How to filter by indexed field and order by creation time?

Did you find this page helpful?