subquery.ts — drizzle-orm Source File

Source Code

import { Expect } from 'type-tests/utils.ts';
import { alias, int, mysqlTable, serial, text } from '~/mysql-core/index.ts';
import { and, eq } from '~/sql/expressions/index.ts';
import { count } from '~/sql/functions/aggregate.ts';
import { sql } from '~/sql/sql.ts';
import type { DrizzleTypeError, Equal } from '~/utils.ts';
import { db } from './db.ts';

const users = mysqlTable('names', {
	id: serial('id').primaryKey(),
	name: text('name'),
	managerId: int('author_id'),
});

const posts = mysqlTable('posts', {
	id: serial('id').primaryKey(),
	authorId: int('author_id'),
	title: text('title'),
});

const n1 = db
	.select({
		id: users.id,
		name: users.name,
		authorId: users.managerId,
		count1: sql<number>`count(1)::int`.as('count1'),
	})
	.from(users)
	.groupBy(users.id, users.name, users.managerId)
	.as('n1');

const n2 = db
	.select({
		id: users.id,
		authorId: users.managerId,
		totalCount: sql<number>`count(1)::int`.as('totalCount'),
	})
	.from(users)
	.groupBy(users.id, users.managerId)
	.as('n2');

const result = await db
	.select({
		name: n1.name,
		authorId: n1.authorId,
		count1: n1.count1,
		totalCount: n2.totalCount,
	})
	.from(n1)
	.innerJoin(n2, and(eq(n2.id, n1.id), eq(n2.authorId, n1.authorId)));

Expect<
	Equal<
		{
			name: string | null;
			authorId: number | null;
			count1: number;
			totalCount: number;
		}[],
		typeof result
// ... (65 more lines)