What's the best way to dynamically filter a query?

[JamesSingleton]

To give some background context, I have a set of filters that all default to "All"

I have a huge function that queries my DB

export async function getTransferPortalEntries(
  page: number = 1,
  pageSize: number = 20,
  filters: TransferPortalFilters = {},
) {
  const offset = (page - 1) * pageSize

  // Create aliases for the schoolReferences table
  const previousSchool = alias(schoolReferences, 'previous_school')
  const commitmentSchool = alias(schoolReferences, 'commitment_school')

  let query = db
    .select({
      id: transferPortalEntries.id,
      entryDate: transferPortalEntries.entryDate,
      transferStatus: transferPortalEntries.transferStatus,
      isGradTransfer: transferPortalEntries.isGradTransfer,
      createdAt: transferPortalEntries.createdAt,
      updatedAt: transferPortalEntries.updatedAt,
      lastStatusChangeAt: transferPortalEntries.lastStatusChangeAt,
      firstName: players.firstName,
      lastName: players.lastName,
      height: players.height,
      weight: players.weight,
      highSchool: players.highSchool,
      hometown: players.hometown,
      state: players.state,
      playerImageUrl: players.playerImageUrl,
      instagramHandle: players.instagramHandle,
      twitterHandle: players.twitterHandle,
      position: positions.name,
      positionAbbreviation: positions.abbreviation,
      classYear: classYears.name,
      classYearAbbreviation: classYears.abbreviation,
      previousSchoolName: previousSchool.name,
      previousSchoolSanityId: previousSchool.sanityId,
      commitmentSchoolName: commitmentSchool.name,
      commitmentSchoolSanityId: commitmentSchool.sanityId,
      commitmentDate: transferPortalEntries.commitmentDate,
    })
    .from(transferPortalEntries)
    .innerJoin(players, eq(transferPortalEntries.playerId, players.id))
    .innerJoin(positions, eq(players.positionId, positions.id))
    .innerJoin(classYears, eq(players.classYearId, classYears.id))
    .innerJoin(previousSchool, eq(transferPortalEntries.previousSchoolId, previousSchool.id))
    .leftJoin(commitmentSchool, eq(transferPortalEntries.commitmentSchoolId, commitmentSchool.id))

  if (filters.year) {
    query = query.where(
      sql`EXTRACT(YEAR FROM ${transferPortalEntries.entryDate}) = ${filters.year}`,
    )
  }
  if (filters.status && filters.status !== 'All') {
    query = query.where(eq(transferPortalEntries.transferStatus, filters.status))
  }
  if (filters.isGradTransfer !== undefined) {
    query = query.where(eq(transferPortalEntries.isGradTransfer, filters.isGradTransfer))
  }
  if (filters.positionId) {
    query = query.where(eq(players.positionId, filters.positionId))
  }
  if (filters.schoolId) {
    query = query.where(
      or(
        eq(transferPortalEntries.previousSchoolId, filters.schoolId),
        eq(transferPortalEntries.commitmentSchoolId, filters.schoolId),
      ),
    )
  }

  const entries = await query
    .orderBy(desc(transferPortalEntries.lastStatusChangeAt), desc(transferPortalEntries.createdAt))
    .limit(pageSize)
    .offset(offset)

  console.log({ entries })

  // Fetch school images from Sanity
  const schoolSanityIds = [
    ...new Set(
      entries
        .map((e) => e.previousSchoolSanityId)
        .concat(
          entries.map((e) => e.commitmentSchoolSanityId).filter((id): id is string => id !== null),
        ),
    ),
  ]
  const schoolImages = await getSchoolsBySanityIds(schoolSanityIds)

  // Combine database results with Sanity images
  return entries.map((entry) => ({
    ...entry,
    previousSchool: schoolImages[entry.previousSchoolSanityId],
    commitmentSchool: entry.commitmentSchoolSanityId
      ? schoolImages[entry.commitmentSchoolSanityId]
      : null,
  }))
}

My schemas for these tables looks like

export const transferStatusEnum = pgEnum('transfer_status', ['Entered', 'Committed', 'Withdrawn'])

export const positions = pgTable(
  'positions',
  {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
    abbreviation: varchar('abbreviation', { length: 10 }).notNull(),
  },
  (table) => [
    uniqueIndex('name_idx').on(table.name),
    uniqueIndex('abbreviation_idx').on(table.abbreviation),
  ],
)

export const classYears = pgTable(
  'class_years',
  {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
    abbreviation: varchar('abbreviation', { length: 10 }).notNull(),
  },
  (table) => [
    uniqueIndex('class_year_name_idx').on(table.name),
    uniqueIndex('class_year_abbreviation_idx').on(table.abbreviation),
  ],
)

export const schoolReferences = pgTable(
  'school_references',
  {
    id: serial('id').primaryKey(),
    sanityId: text('sanity_id').notNull().unique(),
    name: text('name').notNull(),
  },
  (table) => [
    uniqueIndex('sanity_id_idx').on(table.sanityId),
    uniqueIndex('school_name_idx').on(table.name),
  ],
)

export const players = pgTable(
  'players',
  {
    id: serial('id').primaryKey(),
    firstName: text('first_name').notNull(),
    lastName: text('last_name').notNull(),
    height: integer('height'), // in inches
    weight: integer('weight'), // in pounds
    highSchool: text('high_school'),
    hometown: text('hometown'),
    state: varchar('state', { length: 2 }),
    playerImageUrl: text('player_image_url'),
    instagramHandle: varchar('instagram_handle', { length: 30 }),
    twitterHandle: varchar('twitter_handle', { length: 30 }),
    positionId: integer('position_id').references(() => positions.id),
    currentSchoolId: integer('current_school_id').references(() => schoolReferences.id),
    classYearId: integer('class_year_id').references(() => classYears.id),
  },
  (table) => [
    uniqueIndex('players_name_idx').on(table.lastName, table.firstName),
    uniqueIndex('players_social_media_idx').on(table.instagramHandle, table.twitterHandle),
  ],
)

export const transferPortalEntries = pgTable(
  'transfer_portal_entries',
  {
    id: serial('id').primaryKey(),
    playerId: integer('player_id')
      .references(() => players.id)
      .notNull(),
    entryDate: date('entry_date').notNull(),
    transferStatus: transferStatusEnum('transfer_status').notNull().default('Entered'),
    isGradTransfer: boolean('is_grad_transfer').notNull().default(false),
    previousSchoolId: integer('previous_school_id')
      .references(() => schoolReferences.id)
      .notNull(),
    commitmentSchoolId: integer('commitment_school_id').references(() => schoolReferences.id),
    commitmentDate: date('commitment_date'),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow(),
    lastStatusChangeAt: timestamp('last_status_change_at').notNull().defaultNow(),
  },
  (table) => [
    uniqueIndex('unique_player_entry').on(table.playerId, table.entryDate),
    uniqueIndex('transfer_portal_entries_date_idx').on(table.entryDate),
  ],
)

A couple of issues I am running into is the status filter when it's "All" errors as it's not a proper enum which makes sense. Also, I am running into a type error doing the query = query.where

Property 'where' is missing in type 'Omit<import("/Users/jamessingleton/Code/Businesses/redshirt-sports/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_@types_oydy3vileqrvfkrrqmsazssq7y/node_modules/drizzle-orm/pg-core/query-builders/select").PgSelectBase<"transfer_portal_entries", { id: import(...' but required in type 'Omit<import("/Users/jamessingleton/Code/Businesses/redshirt-sports/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_@types_oydy3vileqrvfkrrqmsazssq7y/node_modules/drizzle-orm/pg-core/query-builders/select").PgSelectBase<"transfer_portal_entries", { id: import(...'.ts(2741)
select.d.ts(402, 5): 'where' is declared here.

Also any advise on querying better (if there is a way) would be great as well!