Drizzle Team•2mo ago

I'm trying using $count, and it giving me fieldAlias missing. What should I do?

I'm on drizzle-orm 0.36.1 and drizzle-kit 0.28.0. My schema is something like below. For note, its oversimplified over my project.

export const item = pgTable(
  "items",
  {
    id: text("id").primaryKey().unique().notNull(),
    userId: text("user_id").notNull(),
    name: text("name").notNull(),
  },
);

export const history = pgTable(
  "purchased_items",
  {
    id: text("id").primaryKey().unique().notNull(),
    userId: text("user_id").notNull(),
    itemId: text("item_id")
      .references(() => items.id)
      .notNull(),
    anotherData: text("another_data").notNull()
  },
);

export const historyRelation = relations(history, ({ one }) => ({
  item: one(item, {
    fields: [history.itemId],
    references: [item.id],
  }),
}));

export const itemRelations = relations(item, ({ many }) => ({
  histories: many(history),
}));

export const item = pgTable(
  "items",
  {
    id: text("id").primaryKey().unique().notNull(),
    userId: text("user_id").notNull(),
    name: text("name").notNull(),
  },
);

export const history = pgTable(
  "purchased_items",
  {
    id: text("id").primaryKey().unique().notNull(),
    userId: text("user_id").notNull(),
    itemId: text("item_id")
      .references(() => items.id)
      .notNull(),
    anotherData: text("another_data").notNull()
  },
);

export const historyRelation = relations(history, ({ one }) => ({
  item: one(item, {
    fields: [history.itemId],
    references: [item.id],
  }),
}));

export const itemRelations = relations(item, ({ many }) => ({
  histories: many(history),
}));

And my queries is something like this:

const result = await db.query.item.findMany({
  where: eq(item.userId, requesterId),
  columns: {
    id: true,
    name: true,
  },
  extras: {
    historyCount: db.$count(history, eq(history.itemId, items.id))
  }
});

const result = await db.query.item.findMany({
  where: eq(item.userId, requesterId),
  columns: {
    id: true,
    name: true,
  },
  extras: {
    historyCount: db.$count(history, eq(history.itemId, items.id))
  }
});

Exactly on historyCount my editor giving me typescript error something like this:

Property 'fieldAlias' is missing in type 'PgCountBuilder<PgSession<any, any, any>>' but required in type 'Aliased<unknown>'.ts(2741)
sql.d.ts(184, 18): 'fieldAlias' is declared here.

(property) purchaseCount: PgCountBuilder<PgSession<any, any, any>>

Property 'fieldAlias' is missing in type 'PgCountBuilder<PgSession<any, any, any>>' but required in type 'Aliased<unknown>'.ts(2741)
sql.d.ts(184, 18): 'fieldAlias' is declared here.

(property) purchaseCount: PgCountBuilder<PgSession<any, any, any>>

I enable logger and my raw query is something like this. I make a little modified below, just so the table name will match my example above, everything else is intact

Query: select "id", "name", select count(*) as count from "history" where "item"."item_id" = "item"."id"; as "undefined" from "item" where ("item"."user_id" = $1 and "item"."id" in (select "item_id" from "history" where "history"."user_id" = $2)) -- params: ["u-rXH4ULxe6d", "u-rXH4ULxe6d"]

Query: select "id", "name", select count(*) as count from "history" where "item"."item_id" = "item"."id"; as "undefined" from "item" where ("item"."user_id" = $1 and "item"."id" in (select "item_id" from "history" where "history"."user_id" = $2)) -- params: ["u-rXH4ULxe6d", "u-rXH4ULxe6d"]

4 Replies

kgeOP•2mo ago

For additional info, I also wrap my query into trycatch and log the error. It throw something like this:

error: syntax error at or near "select"
    at <ommitted-path>\node_modules\.pnpm\[email protected][email protected]\node_modules\pg-pool\index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async eval (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected][email protected]/node_modules/drizzle-orm/node-postgres/session.js:96:22)
    at async <ommitted-path> (webpack-internal:///(rsc)/./src/<ommitted-path>)
    at async Page (webpack-internal:///(rsc)/./src/<ommitted-path>) 
{
  length: 95,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '22',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1244',
  routine: 'scanner_yyerror'
}

error: syntax error at or near "select"
    at <ommitted-path>\node_modules\.pnpm\[email protected][email protected]\node_modules\pg-pool\index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async eval (webpack-internal:///(rsc)/./node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected][email protected]/node_modules/drizzle-orm/node-postgres/session.js:96:22)
    at async <ommitted-path> (webpack-internal:///(rsc)/./src/<ommitted-path>)
    at async Page (webpack-internal:///(rsc)/./src/<ommitted-path>) 
{
  length: 95,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '22',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1244',
  routine: 'scanner_yyerror'
}

François•2mo ago

Shouldnt it be

historyCount: db.$count(history, eq(history.itemId, item.id))

historyCount: db.$count(history, eq(history.itemId, item.id))

bart•2mo ago

You need to do this: postCount: db.$count(post, eq(post.user, user.id)).as('postCount') But there's a bug in the $count function see: https://github.com/drizzle-team/drizzle-orm/issues/3493

GitHub

[BUG]: db.$count inside relational query "extras" generates SQL wit...

Report hasn't been filed before. I have verified that the bug I'm about to report hasn't been filed before. What version of drizzle-orm are you using? 0.36.0 What version of drizzle-kit...

kgeOP•2mo ago

I make small typo while making this post 🤣, since i'm making brand new example based on my original code.. But I've double check and I'm sure 100% there is no typo on my original code Adding as fixed the issue i encountered. Thanks for the heads up

Gaming

Programming

I'm trying using $count, and it giving me fieldAlias missing. What should I do?