Kysely•14mo ago

Reusable CTEs that depend on previous CTEs

I have 2 CTEs that I'd like to separate out into separate functions for readability. These are the CTEs:

.with("eligibleChats", (db) =>
  db
    .selectFrom("chatMessages")
    .innerJoin("chats", "chats.id", "chatMessages.chatId")
    .innerJoin("bots", "bots.id", "chats.botId")
    .where("bots.name", "=", botName)
    .groupBy("chatMessages.chatId")
    .having(({ fn }) => fn.countAll(), ">", 1)
    .select("chatMessages.chatId"),
)
.with("lastMessages", (db) =>
  db
    .selectFrom("chatMessages")
    .innerJoin(
      "eligibleChats",
      "eligibleChats.chatId",
      "chatMessages.chatId",
    )
    .where("chatMessages.position", "=", ({ selectFrom }) =>
      selectFrom("chatMessages as innerChatMessages")
        .whereRef("innerChatMessages.chatId", "=", "chatMessages.chatId")
        .select(({ fn }) =>
          fn.max("innerChatMessages.position").as("maxPosition"),
        ),
    )
    .select(["chatMessages.position", "chatMessages.chatId"]),
)

.with("eligibleChats", (db) =>
  db
    .selectFrom("chatMessages")
    .innerJoin("chats", "chats.id", "chatMessages.chatId")
    .innerJoin("bots", "bots.id", "chats.botId")
    .where("bots.name", "=", botName)
    .groupBy("chatMessages.chatId")
    .having(({ fn }) => fn.countAll(), ">", 1)
    .select("chatMessages.chatId"),
)
.with("lastMessages", (db) =>
  db
    .selectFrom("chatMessages")
    .innerJoin(
      "eligibleChats",
      "eligibleChats.chatId",
      "chatMessages.chatId",
    )
    .where("chatMessages.position", "=", ({ selectFrom }) =>
      selectFrom("chatMessages as innerChatMessages")
        .whereRef("innerChatMessages.chatId", "=", "chatMessages.chatId")
        .select(({ fn }) =>
          fn.max("innerChatMessages.position").as("maxPosition"),
        ),
    )
    .select(["chatMessages.position", "chatMessages.chatId"]),
)

I know I can extract the first one like this:

const eligibleChatsCte = (db: QueryCreator<DB>) =>
  db
    .selectFrom("chatMessages")
    .innerJoin("chats", "chats.id", "chatMessages.chatId")
    .innerJoin("bots", "bots.id", "chats.botId")
    .where("bots.name", "=", botName)
    .groupBy("chatMessages.chatId")
    .having(({ fn }) => fn.countAll(), ">", 1)
    .select("chatMessages.chatId");

const eligibleChatsCte = (db: QueryCreator<DB>) =>
  db
    .selectFrom("chatMessages")
    .innerJoin("chats", "chats.id", "chatMessages.chatId")
    .innerJoin("bots", "bots.id", "chats.botId")
    .where("bots.name", "=", botName)
    .groupBy("chatMessages.chatId")
    .having(({ fn }) => fn.countAll(), ">", 1)
    .select("chatMessages.chatId");

But if I do the same for the second one, it won't be able to reference the chatMessages table . Is there a way to do that? The most relevant docs I found were https://kysely.dev/docs/recipes/expressions, but it doesn't cover this usecase. Thanks in advance!

Solution:

If you meant eligibleChats instead of chatMessages, then you can do it like this

QueryCreator<DB & { eligibleChats: { chatId: string } }>

QueryCreator<DB & { eligibleChats: { chatId: string } }>

...

Jump to solution

3 Replies

koskimas•14mo ago

But if I do the same for the second one, it won't be able to reference the chatMessages table . Is there a way to do that?

Why wouldn't it be? The first one is able to reference it, so why wouldn't the second one be?

Solution

koskimas•14mo ago

If you meant eligibleChats instead of chatMessages, then you can do it like this

QueryCreator<DB & { eligibleChats: { chatId: string } }>

QueryCreator<DB & { eligibleChats: { chatId: string } }>

lukas.slezeviciusOP•14mo ago

Yes, I did mean eligibleChats. Apologies! Thanks a lot! In hindsight, it's obvious that there's no other way for a reusable function to know about the existence of some entity (table?) without extending the DB type.

Gaming

Programming

Reusable CTEs that depend on previous CTEs

Did you find this page helpful?