Skip to content

Instantly share code, notes, and snippets.

@choutkamartin
Forked from rphlmr/helpers.ts
Created February 14, 2024 00:58
Show Gist options
  • Save choutkamartin/67c2b499b70b07d47e73394df472aad2 to your computer and use it in GitHub Desktop.
Save choutkamartin/67c2b499b70b07d47e73394df472aad2 to your computer and use it in GitHub Desktop.
Drizzle ORM, deep sub queries
/* -------------------------------------------------------------------------- */
/* More here; */
/* -------------------------------------------------------------------------- */
//gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15
https: 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}',`));
// json_build_object formats to ISO 8601 ...
if (is(value, PgTimestampString)) {
chunks.push(sql`timezone('UTC', ${value})`);
} else {
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
)}), '${sql`[]`}')`;
}
export function inJsonArray<T extends SQL.Aliased<unknown[]>>(
jsonArray: T,
key: keyof T["_"]["type"][number],
values: string[]
) {
const element = sql.raw(`${String(key)}_array_element`);
return sql`EXISTS (
SELECT 1
FROM jsonb_array_elements(${jsonArray}) AS ${element}
WHERE ${inArray(sql`${element}->>${key}`, values)}
)`;
}
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.id).mapWith(String).as("tag_link_id"),
tagId: sql`${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.id)
.as("tag_query");
const tagsQuery = db
.select({
fileId: tagQuery.fileId,
tags: jsonAggBuildObject({
id: tagQuery.tagId,
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));
const filterByTagId = await db
.select({
...getTableColumns(file),
comments: commentsQuery.comments,
tags: tagsQuery.tags,
})
.from(file)
// this line is how to filter
.where(inJsonArray(tagsQuery.tags, "id", [tagIdFilter]))
.leftJoin(commentsQuery, eq(commentsQuery.fileId, file.id))
.leftJoin(tagsQuery, eq(tagsQuery.fileId, file.id));
console.log(JSON.stringify(result, null, 2));
[
{
"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"
}
]
}
]
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",
{
id: uuid("id").notNull().defaultRandom(),
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(),
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment