Drizzle ORM, deep sub queries

helpers.ts

/* -------------------------------------------------------------------------- */
/*                                 More here;                                 */
/* -------------------------------------------------------------------------- */
// https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15

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>>`json_build_object(${sql.join(
    chunks
  )})`;
}

export function jsonAggBuildObject<
  T extends SelectedFields,
  Column extends AnyColumn,
>(
  shape: T,
  options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } },
) {
  return sql<SelectResultFields<T>[]>`coalesce(
    json_agg(${jsonBuildObject(shape)}
    ${
      options?.orderBy
        ? sql`ORDER BY ${options.orderBy.colName} ${sql.raw(
            options.orderBy.direction,
          )}`
        : undefined
    })
    FILTER (WHERE ${and(
      sql.join(
        Object.values(shape).map((value) => sql`${sql`${value}`} IS NOT NULL`),
        sql` AND `,
      ),
    )})
    ,'${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)}
	  )`;
}

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.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));

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",
	{
		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(),
});

Hey @rphlmr, thanks for the helper fns, those are lifesavers!
I'm wondering why the filter expression for jsonAggBuildObject is so strict about nulls tho and drops every row with a single null column value. Is there a practical reason for that I'm missing? Thanks!

FILTER (WHERE ${and(
      sql.join(
        Object.values(shape).map((value) => sql`${sql`${value}`} IS NOT NULL`),
        sql` AND `,
      ),
    )})

@warflash 👋 Actually, it could be an option. Nothing intentional, I guess I just copied and pasted what I had in my project (where I needed non-null on multiple left joins).

Hello @rphlmr,

Thank you for the helpers 🙂

Do you think they will be built-in one day with drizzle orm?

Moreover, do you plan to have a helper for row_to_json from postgres? I am using it with the so-called Magical sql operator, but it doesn't keep the types. I try to use row_to_json for tables, subqueries. I have hard time to know how to extract the types and keep them when I use the final result with trpc.

PS: On line 6 of the helpers.ts, there is a typo with "https: " that you included on this line.
PS2: I tried to use getSubQueryColumns but VS Code doesn't find SubqueryConfig. Do you know what the issue is?

@etx121 Hello 👋
Thanks for your support.

I have an other gist: https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15 with more stuff

There is also a community library: https://github.com/iolyd/drizzle-orm-helpers, but it is warned as maybe not production-ready. I talked with the Drizzle Team, and it's hard to know if it is possible to have something official (time and resources).
I want to do that though, based on Iolyd's work. Since I am also maintaining https://drizzle.run, I have to find more time 😬

export function getSubQueryColumns<
  S extends ColumnsSelection,
  A extends string,
>(subQuery: SubqueryWithSelection<S, A> | WithSubqueryWithSelection<S, A>) {
  const { selection } = subQuery as unknown as {
    selection: (typeof subQuery)["_"]["selectedFields"];
  };

  return selection;
}

Should be good. Will update my gists

Hi @rphlmr ,
Thanks for the quick reply.
I checked the drizzle-orm-helpers. it looks good, but I still cannot keep the types when I use sqlrow_to_json(myTable), especially when I nest row_to_json into another one. I am trying to find a solution to keep the type when I query the data with trpc.

How about this to include the row if at least one field is not null?

export function jsonAggBuildObject<T extends SelectedFields, Column extends AnyColumn>(
   shape: T,
   options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } }
) {
   const orderByClause = options?.orderBy
      ? sql`ORDER BY ${options.orderBy.colName} ${sql.raw(options.orderBy.direction)}`
      : sql``;

   const filterConditions = Object.values(shape).map(
      (value) => sql`${sql`${value}`} IS NOT NULL`
   );
   const filterClause =
      filterConditions.length > 0 ? sql.join(filterConditions, sql` OR `) : sql`TRUE`;

   return sql<SelectResultFields<T>[]>`coalesce(
      json_agg(${jsonBuildObject(shape)} ${orderByClause})
      FILTER (WHERE ${filterClause}),
      '${sql`[]`}'
   )`;
}

@rphlmr I figured out after a few days how to deal with subqueries from CTEs where I need to have nested JSON. At the end, getSubQueryColumns was not necessary, and getColumns built in the drizzle-orm-helpers was working well.
For the people interested in that, I used the npm package drizzle-orm-helpers with

• getColumns
• jsonbBuildObject

It looks like:

db
.select({
   ...getColumns(table1),
   newFieldName: jsonbBuildObject({
      ...getColumns(table2),
   }).as("newFieldName"),
})
.from(table1)

How about this to include the row if at least one field is not null?

export function jsonAggBuildObject<T extends SelectedFields, Column extends AnyColumn>(
   shape: T,
   options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } }
) {
   const orderByClause = options?.orderBy
      ? sql`ORDER BY ${options.orderBy.colName} ${sql.raw(options.orderBy.direction)}`
      : sql``;

   const filterConditions = Object.values(shape).map(
      (value) => sql`${sql`${value}`} IS NOT NULL`
   );
   const filterClause =
      filterConditions.length > 0 ? sql.join(filterConditions, sql` OR `) : sql`TRUE`;

   return sql<SelectResultFields<T>[]>`coalesce(
      json_agg(${jsonBuildObject(shape)} ${orderByClause})
      FILTER (WHERE ${filterClause}),
      '${sql`[]`}'
   )`;
}

@tobychidi Yes! I have this in an other gist: https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15#file-jsonagg_jsonaggbuildobject-ts-L39

Many variations exist depending on the need. We are thinking about publishing these helpers in a tested package, all ideas are welcome!
Not sure about a 'when' but we are thinking about that with drizzle-orm-helpers creator!

How about this to include the row if at least one field is not null?

export function jsonAggBuildObject<T extends SelectedFields, Column extends AnyColumn>(
   shape: T,
   options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } }
) {
   const orderByClause = options?.orderBy
      ? sql`ORDER BY ${options.orderBy.colName} ${sql.raw(options.orderBy.direction)}`
      : sql``;

   const filterConditions = Object.values(shape).map(
      (value) => sql`${sql`${value}`} IS NOT NULL`
   );
   const filterClause =
      filterConditions.length > 0 ? sql.join(filterConditions, sql` OR `) : sql`TRUE`;

   return sql<SelectResultFields<T>[]>`coalesce(
      json_agg(${jsonBuildObject(shape)} ${orderByClause})
      FILTER (WHERE ${filterClause}),
      '${sql`[]`}'
   )`;
}

@tobychidi Yes! I have this in an other gist: https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15#file-jsonagg_jsonaggbuildobject-ts-L39

Many variations exist depending on the need. We are thinking about publishing these helpers in a tested package, all ideas are welcome! Not sure about a 'when' but we are thinking about that with drizzle-orm-helpers creator!

Sounds exciting

I came across another problem.

1. It seems we lose Drizzle's date-string to date-object conversion when we use jsonBuildObject?

2. I am using multiple jsonAggBuildObject for multiple different joins in on a select query. I am getting a kind of duplication with some of the returned columns.

Fixed:

2. I fixed this issue with subqueries. The problem is more about my newness to SQL than an issue with Drizzle itself. So, I do the aggregation in subqueries (which I like actually) and then place the results in the main query. Good learning!

How about arrayAgg in jsonAggBuildObject? Right now I am etting this error : aggregate functions are not allowed in FILTER

@tobychidi You can split the query: https://drizzle.run/se2noay5mhdu24va3xhv0lqo

const postsWithTagsQuery = db.$with("posts_with_tags_query").as(
  db
    .select({
      id: posts.id,
      title: posts.title,
      authorId: posts.authorId,
      tags: arrayAgg(tags.label).as("tags"),
    })
    .from(posts)
    .leftJoin(postsTags, eq(posts.id, postsTags.postId))
    .innerJoin(tags, eq(tags.id, postsTags.tagId))
    .groupBy(posts.id),
);

const result = await db
  .with(postsWithTagsQuery)
  .select({
    ...getTableColumns(users),
    posts: jsonAggBuildObject(postsWithTagsQuery._.selectedFields),
  })
  .from(users)
  .leftJoin(postsWithTagsQuery, eq(postsWithTagsQuery.authorId, users.id))
  .groupBy(users.id);

Thank you @rphlmr. I didn't see this earlier but subqueries did solve the problem. I didn't use the $with API though just db.select().as() then joined it. Is it any different if to use $with?

@tobychidi 🫡
$with is just like a variable to make the query easier to read, replacing duplications by an alias