DT
Drizzle Team•4d ago
jsingleton37

Running into some type errors with query.where

I have a massive query that grabs rows from a table called transfer_portal_entries and joins them with a couple other tables to get the players information back that is needed to populate a page. However, when it comes to the if statements around the filters, query is underlined saying 
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.
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.
However, I am not entirely sure how to resolve this.
No description
7 Replies
Mario564
Mario564•3d ago
Here's how you solve this: 1. Create an empty array like this: 
const filters: (SQL | undefined)[] = [];
const filters: (SQL | undefined)[] = [];
2. Add your filters before building the query by doing filters.push. 3. Spread the array in the where method of the query (.where(...filters))
jsingleton37
jsingleton37OP•3d ago
Is that before building the original query?
Mario564
Mario564•3d ago
Yes, before building query
Mario564
Mario564•3d ago
Here are some docs that go in a bit more detail: https://orm.drizzle.team/docs/guides/conditional-filters-in-query
Drizzle ORM - Conditional filters in query
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
jsingleton37
jsingleton37OP•3d ago
Odd, I did 
const filtersArray: (SQL | undefined)[] = []
  if (filters.year) {
    filtersArray.push(sql`EXTRACT(YEAR FROM ${transferPortalEntries.entryDate}) = ${filters.year}`)
  }
  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))
    .where(and(...filtersArray))
const filtersArray: (SQL | undefined)[] = []
  if (filters.year) {
    filtersArray.push(sql`EXTRACT(YEAR FROM ${transferPortalEntries.entryDate}) = ${filters.year}`)
  }
  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))
    .where(and(...filtersArray))
And nothing returns, if I remove the .where() it returns data. The current filters are { filters: { year: 2025, status: 'All' } } being passed in. I also have an issue where status: 'All' won't return becuase of 
invalid input value for enum transfer_status: "All"
invalid input value for enum transfer_status: "All"
So need to figure that out as well
Mario564
Mario564•3d ago
It may have to do with the logic rather than being something syntactical Have you pushed the latest changes to your DB?
jsingleton37
jsingleton37OP•3d ago
Yea I have done drizzle-kit push for my schemas or else the original query minus the .where() wouldn't return anything. It returns data if I remove the .where() So I think it's something with 
sql`EXTRACT(YEAR FROM ${transferPortalEntries.entryDate}) = ${filters.year}`
sql`EXTRACT(YEAR FROM ${transferPortalEntries.entryDate}) = ${filters.year}`
Added a different filter and that works just fine And that schema for transferPortalEntries looks like 
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),
  ],
)
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),
  ],
)
So there is an entryDate
Want results from more Discord servers?
Add your server