Drizzle ORM, deep sub queries

helpers.ts

export function distinctOn<Column extends AnyColumn>(column: Column) {
	return sql<Column["_"]["data"]>`distinct on (${column}) ${column}`;
}

export function jsonBuildObject<T extends SelectedFields>(shape: T) {
	const chunks: SQL[] = [];

	Object.entries(shape).forEach(([key, value]) => {
		if (chunks.length > 0) {
			chunks.push(sql.raw(`,`));
		}
		chunks.push(sql.raw(`'${key}',`));
		chunks.push(sql`${value}`);
	});

	return sql<SelectResultFields<T>>`coalesce(json_build_object(${sql.join(
		chunks,
	)}), '{}')`;
}

export function jsonAggBuildObject<T extends SelectedFields>(shape: T) {
	return sql<SelectResultFields<T>[]>`coalesce(jsonb_agg(${jsonBuildObject(
		shape,
	)}), '[]')`;
}

query.ts

const commentQuery = db
		.select({
			id: distinctOn(comment.id).mapWith(String).as("comment_id"),
			fileId: sql`${comment.fileId}`.mapWith(String).as("file_id"),
			text: comment.text,
			commenter: {
				id: sql`${user.id}`.mapWith(String).as("commenter_id"),
				name: user.name,
			},
		})
		.from(comment)
		.innerJoin(user, eq(comment.commenterId, user.id))
		.orderBy(comment.id)
		.as("comment_query");

	const commentsQuery = db
		.select({
			fileId: commentQuery.fileId,
			comments: jsonAggBuildObject({
				id: commentQuery.id,
				text: commentQuery.text,
				commenter: jsonBuildObject({
					id: commentQuery.commenter.id,
					name: commentQuery.commenter.name,
				}),
			}).as("comments"),
		})
		.from(commentQuery)
		.groupBy(commentQuery.fileId)
		.as("comments_query");

	const tagQuery = db
		.select({
			id: distinctOn(tagsPivot.tagId).mapWith(String).as("tag_id"),
			fileId: sql`${tagsPivot.fileId}`
				.mapWith(String)
				.as("tagged_file_id"),
			name: tag.name,
		})
		.from(tagsPivot)
		.innerJoin(tag, eq(tag.id, tagsPivot.tagId))
		.orderBy(tagsPivot.tagId)
		.as("tag_query");

	const tagsQuery = db
		.select({
			fileId: tagQuery.fileId,
			tags: jsonAggBuildObject({
				id: tagQuery.id,
				name: tagQuery.name,
			}).as("tags"),
		})
		.from(tagQuery)
		.groupBy(tagQuery.fileId)
		.as("tags_query");

	const result = await db
		.select({
			...getTableColumns(file),
			comments: commentsQuery.comments,
			tags: tagsQuery.tags,
		})
		.from(file)

		.leftJoin(commentsQuery, eq(commentsQuery.fileId, file.id))
		.leftJoin(tagsQuery, eq(tagsQuery.fileId, file.id));

	console.log(JSON.stringify(result, null, 2));

result.json

[
  {
    "id": "84d88f3c-ff46-4b52-90b8-3d8838fa0c75",
    "name": "Jedi code",
    "comments": [
      {
        "id": "b7190643-4b2d-4412-a438-3e440f4f5322",
        "text": "Learn that my apprentice",
        "commenter": {
          "id": "01d1fd7a-10a8-4760-900d-72b201d51b45",
          "name": "Obi-Wan"
        }
      }
    ],
    "tags": [
      {
        "id": "f19f5f1c-5efb-4e50-ba53-882274529659",
        "name": "padawan"
      }
    ]
  },
  {
    "id": "592998ec-f0d3-43ff-8ed3-ab10ee8522de",
    "name": "Sith code",
    "comments": [
      {
        "id": "46d1b4c3-89dd-49f1-9159-53eb32372b79",
        "text": "Agree I am",
        "commenter": {
          "id": "6c9f3c61-9f18-44ed-90f7-90b97a1776fa",
          "name": "Yoda"
        }
      },
      {
        "id": "e3d3d03c-4ae3-4e38-b866-224e29c35fbb",
        "text": "We should hide that from padawan",
        "commenter": {
          "id": "01d1fd7a-10a8-4760-900d-72b201d51b45",
          "name": "Obi-Wan"
        }
      }
    ],
    "tags": [
      {
        "id": "0917cf03-a56b-4056-bacd-b6ac84e3adf4",
        "name": "knight"
      },
      {
        "id": "330b7583-ce67-4ce0-aacc-8e34030f75f0",
        "name": "master"
      }
    ]
  }
]

schema.ts

export const user = pgTable("user", {
	id: uuid("id").primaryKey().defaultRandom(),
	name: text("name").notNull(),
});

export const file = pgTable("file", {
	id: uuid("id").primaryKey().defaultRandom(),
	name: text("name").notNull(),
});

export const tag = pgTable("tag", {
	id: uuid("id").primaryKey().defaultRandom(),
	name: text("name").notNull(),
});

export const tagsPivot = pgTable(
	"tags_pivot",
	{
		fileId: uuid("file_id")
			.references(() => file.id)
			.notNull(),
		tagId: uuid("tag_id")
			.references(() => tag.id)
			.notNull(),
	},
	(t) => ({
		cpk: primaryKey({ columns: [t.fileId, t.tagId] }),
	}),
);

export const comment = pgTable("comment", {
	id: uuid("id").primaryKey().defaultRandom(),
	commenterId: uuid("commenter_id")
		.references(() => user.id)
		.notNull(),
	fileId: uuid("file_id")
		.references(() => file.id)
		.notNull(),
	text: text("text").notNull(),
});