K
Kysely•5w ago
Naltchapol

Select by many IDs query with order based on input

I'm currently trying to write kysely queries to replace direct better-sqlite3 usage in my app, and one of them is a select query which receives list of IDs and outputs objects in the same order as the IDs list currently, i almost have a solution using a with clause, raw sql, and (VALUES (?),(?),...), but i don't know how to type the raw sql part so the output doesn't have any please see https://kyse.link/tPC0N thanks in advance
Solution:
yeah i did saw your thread but i didn't see that you've put another playground link with the solution, thanks! ```ts db .with( "chosenIds(MediaId)",...
Jump to solution
5 Replies
Unknown User
Unknown User•4w ago
Message Not Public
Sign In & Join Server To View
Igal (mobile)
Igal (mobile)•4w ago
Hey 👋 Why not sort and use in and order by ? 😬
Naltchapol
NaltchapolOP•3w ago
(sorry for the late reply, been busy with work)
Solution
Naltchapol
Naltchapol•3w ago
yeah i did saw your thread but i didn't see that you've put another playground link with the solution, thanks! 
db
  .with(
    "chosenIds(MediaId)",
    () =>
      // this takes a generic argument for the shape of a row the query produces
      // not naming it according to what you specified in the with clause makes tsc complain, very interesting!
      sql<{ MediaId: number }>`(VALUES ${sql.join(mediaIds.map((id) => sql`(${id})`))})`,
  )
  .selectFrom("chosenIds")
  .leftJoin("MediaItem", "MediaItem.MediaId", "chosenIds.MediaId")
  .selectAll()
  .execute();
db
  .with(
    "chosenIds(MediaId)",
    () =>
      // this takes a generic argument for the shape of a row the query produces
      // not naming it according to what you specified in the with clause makes tsc complain, very interesting!
      sql<{ MediaId: number }>`(VALUES ${sql.join(mediaIds.map((id) => sql`(${id})`))})`,
  )
  .selectFrom("chosenIds")
  .leftJoin("MediaItem", "MediaItem.MediaId", "chosenIds.MediaId")
  .selectAll()
  .execute();
Naltchapol
NaltchapolOP•3w ago
i want functionality to be identical to existing better-sqlite3 code for now, and i prefer having to do less in JS/TS and more in SQL. that means using IN (...) directly would fail in cases where the input calls for [1, 2, 3, 2, 2, 1] for example and i would have to write some extra code to accommodate this. i know it's not "legit" as row order is arbitrary without ORDER BY but i'm lazy and it seems to work for now and so its a problem for future me to deal with

Did you find this page helpful?