Objection.js: findByIds(...) returns models in a different order

[vdrg]

The findByIds operation doesn't preserve the order of the given array of ids. This happens because FindByIdOperation uses WHERE id IN (...).

There are a few options:

1. Documenting this as expected behavior
2. Making it preserve the order
3. Creating a new operation findByIdsOrdered or similar

I've seen multiple solutions for doing this with SQL, but some are db specific. The one that is supported by most dbs is using an ORDER BY CASE id operation, but don't know about its performance.

I can try to implement this, thoughts on how I should do it?

[koskimas]

Ordering by id isn't a solution. That assumes you gave the ids as an ordered array. I was under the impression that WHERE IN query does preserve the order. At least on postgres. Which db are you using?

Anyway, you can easily do this yourself.

[koskimas]

I'll add a mention in the docs that findByIds doesn't preserve the order.

[vdrg]

Hi @koskimas ! thanks for the quick response :slightly_smiling_face:

I agree that ordering by id isn't a solution, what I suggested in the issue was using ORDER BY CASE like:

SELECT id
FROM pointslocation
WHERE id IN (1,7,3,4,5,2,6)
ORDER BY CASE id
         WHEN 1 THEN 1
         WHEN 7 THEN 2
         WHEN 3 THEN 3
         WHEN 4 THEN 4
         WHEN 5 THEN 5
         WHEN 2 THEN 6
         WHEN 6 THEN 7
         END 

In postgres, WHERE IN doesn't preserve the order. This is actually expected behavior because if no ORDER BY operation is included then (from postgres manual):

the rows are returned in whatever order the system finds fastest to produce.

I just thought that it could be a great feature as many times you need to preserve the order when fetching multiple models, and doing it with javascript after the results are returned could become too expensive, so doing it with the sql query seemed like a good idea.

There are other options to using the ORDER BY CASE clause of course, but usually they are db specific. For example, in postgres you can use JOIN unnest('{1,3,2,4}'::int[]) WITH ORDINALITY t(id, ord) USING (id) and then order by t.ord.

[koskimas]

Sorting using ORDER BY CASE like you suggested will definitely be slower than sorting in javascript. Even generating the SQL for that will probably be slower than sorting in javascript :smile: You can sort tens of thousands of items in milliseconds in javascript.

Also consider the generated SQL when someone passes 1000 ids in findByIds. Also how would that work with composite keys?

[vdrg]

True. Yeah, performance is what worries me :disappointed:. I don't know how it would work with composite keys, that is why I was thinking of creating a different function and maybe making it db specific. Will do it with js for now :+1:

[lynxtaa]

In Postgres we are using this trick:

Users.query()
  .findByIds(ids)
  .orderByRaw('array_position(?, id)', [ids])
  .then(oneToOne(ids, 'id'))

// This function will insert null in positions where user was not found,
// so the output will be the same length as the input (useful for https://github.com/graphql/dataloader)
function oneToOne(ids, key) {
  return function(rows) {
    if (rows.length === ids.length) {
      return rows
    }

    ids.forEach((id, i) => {
      if (!rows[i] || rows[i][key] !== id) {
        rows.splice(i, 0, null)
      }
    })

    return rows
  }
}

[demisx]

@lynxtaa You are the savior! Thank you for such a cool workaround.