kysely lol

gistfile1.txt

      const threads = await ctx.db
        .selectFrom("email")
        .innerJoin("emailContact", "emailContact.emailId", "email.id")
        .select([sql`max(timestamp)`.as("timestamp"), "threadId"])
        .select(["emailContact.contactEmailId"])
        .select((eb) =>
          jsonObjectFrom(
            eb
              .selectFrom("email as e")
              .select(allColumns(Columns.Email, "e"))
              .select((eb) =>
                jsonArrayFrom(
                  eb
                    .selectFrom("emailUser")
                    .innerJoin("user", "user.email", "emailUser.userEmailId")
                    .select([
                      "user.email",
                      "user.id",
                      "user.name",
                      "user.image",
                      "emailUser.role",
                    ])
                    .whereRef("emailUser.emailId", "=", "e.id"),
                ).as("users"),
              )
              .select((eb) =>
                jsonArrayFrom(
                  eb
                    .selectFrom("emailAttachment")
                    .select(
                      allColumns(Columns.EmailAttachment, "emailAttachment"),
                    )
                    .whereRef("emailAttachment.emailId", "=", "e.id"),
                ).as("attachments"),
              )
              .whereRef("e.threadId", "=", "email.threadId")
              .orderBy("e.timestamp", "desc")
              .limit(1),
          ).as("email"),
        )
        .where("emailContact.contactEmailId", "=", input.contactEmailId)
        .orderBy("timestamp", "desc")
        .distinct()
        .groupBy(["threadId", "subject"])
        .$if(!!input.cursor, (qb) =>
          qb.where("email.timestamp", "<=", input.cursor ?? new Date()),
        )
        .execute();