How do I orderBy an array in knex?

EDIT: I've accepted the answer, as it seems like it should work, but I have done a different approach that does not require this anymore

I really need some help figuring this out, how do I orderBy an array in knex?

I select using an array, but the order of the array is important. Using this: knex("users").select("id", "name").whereIn("song_num", [4, 1, 34]) Returns me data in the order of 1, 4, 34. What I want is 4, 1, 34 Is there a way that to order by the original sequence of the array in knex?

I've searched and it's possible for example in MySQL by using FIELD or in PostgresSQL using ORDINALITY. Is there a way to do this in knex? There's also an orderByRaw but I can't seem to get it to work

Example in MySQL: SELECT * FROM comments WHERE comments.id IN ('12','5','3','17') ORDER BY FIELD(comments.id,'12','5','3','17')

Example in Postgres: SELECT c.* FROM comments c JOIN unnest('{1,3,2,4}'::int[]) WITH ORDINALITY t(id, ord) USING (id) ORDER BY t.ord;

Thank you!!! The database I'm using is sqlite if it matters

I tried using a orderByRaw() and putting an array, but it does not seem to accept my array and just crashes

Solution

It doesn't look like sqlite has a nice way of doing this, the suggested method seems to be using a case statement SQLite and custom order by

You could achieve this via orderByRaw by doing something like so:

const songNumbers = [4, 1, 34];
const results = await knex("users")
    .select("id", "name").whereIn("song_num", [4, 1, 34])
    .orderByRaw(`CASE song_num ${songNumbers.map((val, idx) => `WHEN ${val} THEN ${idx}`).join(' ')} END`);

Note this assumes the values in your array are safe to inject directly into the query. If the values are not safe then i'd suggest using positional arguments/bindings instead of directly inserting values.