[Request] Sorting by derived columns

[AJDowds]

I was just wondering if you could provide some information on how you might deal with sorting by some derived column which doesn't actually exist in the database e.g. sort by whether users are currently online (ID exists in an array retrieved by some function).

Sorry if this isn't the correct place to make this request, couldn't see a feature request template. Thanks for the library, very useful.

[charlie-hadden]

@AJDowds Hi there,

I'm assuming you're talking about doing this with the cursor pagination? I have an example of using arbitrary expressions in the test suite here:

kysely-paginate/test/cursor.test.ts

Lines 382 to 403 in ed37a87

	it("works using other expressions", async () => {
	await createSampleBlogPosts(db, 1);
	const expr = db.fn.coalesce("id", "authorId");
	const query = db
	.selectFrom("blogPosts")
	.selectAll()
	.select(expr.as("field"));
	const posts = await query.orderBy(expr, "asc").execute();
	const result = await executeWithCursorPagination(query, {
	perPage: 50,
	after: defaultEncodeCursor<any, any, any, any>([["field", 0]]),
	fields: [{ expression: expr, direction: "asc", key: "field" }],
	parseCursor: z.object({ field: z.coerce.number().int() }),
	});
	expect(result.hasNextPage).toBe(false);
	expect(result.rows).toEqual(posts);
	});

I believe that approach should cover what you're asking for here if I understand correctly. In the example I'm using coalesce as the expression, but there shouldn't be anything stopping you from using something like this:

sql`id in (${sql.join(onlineIds)})`

The expression must be used in the fields option, but also has to be selected so that it can be encoded into the cursor. Also remember that if you're sorting by something like that you'll need to include at least a tie-breaker sort, such as a sort on your id too so that the order remains stable.

If that example doesn't cover your use case, then a more concrete example (perhaps the query you're trying to run, but without the pagination options) would be helpful for me to have.

[AJDowds]

Thanks for the quick response. This is what I've been trying but haven't been able to get it to work:

    const online = await getConnections()
    let expr = sql`id in (${sql.join(online)})`

    let qb = db
      .selectFrom("user")
      .select([
        "user.id",
        "user.email",
        "user.emailConfirmed",
        "user.username",
        "user.firstName",
        "user.lastName",
        "user.dob",
        "user.gender",
        "user.picture",
        "user.createdAt",
        "user.updatedAt",
        expr.as("online"),
      ])
      .where("user.id", "!=", context.user.id)

    if (options.orderBy === "ONLINE") {
      qb.orderBy(expr, "desc")
    }


    const page = await executeWithCursorPagination({
      qb,
      opts: {
        after: after,
        cursorPerRow: true,
        perPage: first,
        fields: [
          { key: "id", expression: "id", direction: "desc" },
          { key: "username", expression: "username", direction: "desc" },
          { key: "online", expression: expr, direction: "desc" },
        ],
        parseCursor: (cursor) => ({
          id: parseInt(cursor.id, 10),
          username: cursor.username,
          online: cursor.online,
        }),
      },
    })

[AJDowds]

Actually, I have it working like this now thanks to following what you had in your test suite:

      const online = await getConnections()
      const expr = db.fn.coalesce(
        db
          .case()
          .when("user.id", "in", online)
          .then("ONLINE")
          .when("user.id", "not in", online)
          .then("OFFLINE")
          .else("OFFLINE")
          .end()
      )
  
      let qb = db
        .selectFrom("user")
        .select([
          "user.id",
          "user.email",
          "user.emailConfirmed",
          "user.username",
          "user.firstName",
          "user.lastName",
          "user.dob",
          "user.gender",
          "user.picture",
          "user.createdAt",
          "user.updatedAt",
          expr.as("status"),
        ])
        .where("user.id", "!=", context.user.id)

      if (options.orderBy === "ONLINE") {
        qb.orderBy(expr, "desc")
      }


    const page = await executeWithCursorPagination({
      qb,
      opts: {
        after: after,
        cursorPerRow: true,
        perPage: first,
        fields: [
          { key: "status", expression: expr, direction: "desc" },
          { key: "updatedAt", expression: "updatedAt", direction: "desc" },
          { key: "id", expression: "id", direction: "desc" },
          { key: "username", expression: "username", direction: "desc" },
        ],
        parseCursor: (cursor) => ({
          status: cursor.status,
          updatedAt: new Date(cursor.updatedAt),
          id: parseInt(cursor.id, 10),
          username: cursor.username,
        }),
      },
    })

Thanks for your help. Can I just ask, if you were dealing with a query that had lots of filtering/sorting conditionally added to it. Would you then just conditionally include elements in the fields array and parseCursor call?