Egon ORM

Appearance

Top N per parent ​

The three most viewed posts of each author, the latest comment of each post: a limit that applies to each parent rather than to the whole result.

Setup ​

The code of this recipe depends on these declarations: the imports, the schema classes, the repo, and the migration that creates the tables. Run the recipe in an empty database of its own: each recipe creates its own tables, and some have the same names (posts) with other columns.

ts
import { postgres } from "@egon-orm/postgres";
import { change, createRepo, from, hasMany, rowNumber, Schema, subquery, t } from "egon-orm";
import { createMigrator, defineMigration } from "egon-orm/migration";

const repo = createRepo({ adapter: postgres({ connectionString: process.env.DATABASE_URL }) });

class Author extends Schema("authors", {
  id: t.integer().primaryKey().dbGenerated(),
  name: t.string(),
}) {
  static posts = hasMany(() => Post, { foreignKey: "authorId" });
}

class Post extends Schema("posts", {
  id: t.integer().primaryKey().dbGenerated(),
  authorId: t.integer(),
  title: t.string(),
  views: t.integer(),
}) {}

// The tables, created by a migration.
const tables = defineMigration({
  up(m) {
    m.createTable("authors", (table) => {
      table.id({ type: "integer" });
      table.text("name").notNull();
    });
    m.createTable("posts", (table) => {
      table.id({ type: "integer" });
      table.references("author_id", "authors", { type: "integer" }).notNull();
      table.text("title").notNull();
      table.integer("views").notNull();
    });
  },
  down(m) {
    m.dropTable("posts");
    m.dropTable("authors");
  },
});

/** Creates the tables, in the database that DATABASE_URL names. */
export async function createTables(): Promise<void> {
  const admin = postgres({ connectionString: process.env.DATABASE_URL });
  await createMigrator({
    adapter: admin,
    migrations: [{ version: 20261021001101, name: "top_n_tables", migration: tables }],
  }).up();
  await admin.close();
}

With a preload ​

The examples below start from these rows, after creating the tables:

ts
await createTables();
const ada = await repo.insertOrThrow(change(Author, { name: "Ada" }));
const bo = await repo.insertOrThrow(change(Author, { name: "Bo" }));
await repo.insertAll(Post, [
  { authorId: ada.id, title: "A1", views: 5 },
  { authorId: ada.id, title: "A2", views: 50 },
  { authorId: ada.id, title: "A3", views: 20 },
  { authorId: ada.id, title: "A4", views: 1 },
  { authorId: bo.id, title: "B1", views: 7 },
]);

When the parents are loaded too, a preload with a limit does it:

ts
// The three most viewed posts of each author: one query for the authors, one for the posts.
const authors = await repo.all(
  from(Author, "a")
    .orderBy(({ a }) => a.name)
    .preload("posts", (q) => q.orderBy(({ posts }) => posts.views.desc()).limit(3)),
);

The limit in a preload callback is per parent. It still runs as one query for all the authors: the posts are numbered per author with row_number() over (partition by …) and filtered on that number. A per-parent limit needs an order, from the callback or the association's own orderBy.

With a window function ​

Without the parents, or to filter or join on the rank, number the rows yourself in a subquery:

ts
// The same rows without the parents: number the posts per author, keep numbers 1 to 3.
const ranked = from(Post, "p").select(({ p }) => ({
  id: p.id,
  rank: rowNumber().over({ partitionBy: [p.authorId], orderBy: [p.views.desc()] }),
}));

const top = await repo.all(
  from(Post, "p")
    .join(subquery(ranked), "r", ({ p, r }) => r.id.eq(p.id))
    .where(({ r }) => r.rank.lte(3))
    .orderBy(({ p, r }) => [p.authorId, r.rank]),
);

Window functions are computed after where, so the rank cannot be filtered in the same query: the subquery computes it, and the outer query filters on it.

Good to know

  • A preload's per-parent limit is refused on a has-through association along which a row can be reached twice, since the row would be counted twice.
  • Ties: rowNumber gives tied rows different numbers in no particular order; add a column to the orderBy (such as id) to make it stable, or use rank to keep ties together.

See also ​