Cannot query DB table get `COALESCE types smallint and text cannot be matched`

When i run the following query

let tc = await db.query.ATable.findFirst({
    where: and(
        eq(ATable.year, year),
        eq(ATable.week, week),
        eq(ATable.ownerId, userId)
    ),
    with: {
        bs: true,
        cs: true
    }
})

let tc = await db.query.ATable.findFirst({
    where: and(
        eq(ATable.year, year),
        eq(ATable.week, week),
        eq(ATable.ownerId, userId)
    ),
    with: {
        bs: true,
        cs: true
    }
})

I get the query error COALESCE types smallint and text cannot be matched. Any thoughts on how I can get around this? Schema posted in the comments

2 Replies

mgarfOP•2y ago

export const ATable = pgTable('a', {
    year: smallint('year').notNull(),
    week: smallint('week').notNull(),
    ownerId: text('ownerId').notNull().references(() => UserTable.id),
    status: StatusEnum('status').notNull(),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
}, (table) => {
    return {
        id: primaryKey(table.year, table.week, table.ownerId),
        getAIndex: index("get_a_idx").on(table.year, table.week, table.ownerId),
    }
});

export const ARelations = relations(ATable, ({ one, many }) => ({
    owner: one(UserTable, {
        fields: [ATable.ownerId],
        references: [UserTable.id]
    }),
    bs: many(BTable),
    cs: many(CTable)
}));

export const ATable = pgTable('a', {
    year: smallint('year').notNull(),
    week: smallint('week').notNull(),
    ownerId: text('ownerId').notNull().references(() => UserTable.id),
    status: StatusEnum('status').notNull(),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
}, (table) => {
    return {
        id: primaryKey(table.year, table.week, table.ownerId),
        getAIndex: index("get_a_idx").on(table.year, table.week, table.ownerId),
    }
});

export const ARelations = relations(ATable, ({ one, many }) => ({
    owner: one(UserTable, {
        fields: [ATable.ownerId],
        references: [UserTable.id]
    }),
    bs: many(BTable),
    cs: many(CTable)
}));

export const BTable = pgTable('b', {
    id: serial('id').primaryKey(),

    // Foreign Key
    year: smallint('year').notNull(),
    week: smallint('week').notNull(),
    ownerId: text('ownerId').notNull(),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
}, (table) => {
    return {
        unq: unique().on(table.year, table.week, table.ownerId, table.project_Id)
    }
});

export const BRelations = relations(BTable, ({ one }) => ({
    As: one(ATable, {
        fields: [BTable.year, BTable.week, BTable.ownerId],
        references: [ATable.year, ATable.week, ATable.ownerId]
    }),
}));

export const CTable = pgTable('C', {
    id: serial('id').primaryKey(),
    who_Id: text('whoId').references(() => UserTable.id),
    manager_Id: text('managerId').references(() => UserTable.id),
    status: StatusEnum('status').notNull(),

    // Foreign Key
    year: smallint('year'),
    week: smallint('week'),
    ownerId: text('ownerId'),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
});

export const CRelations = relations(CTable, ({ one }) => ({
    who: one(UserTable, {
        fields: [CTable.who_Id],
        references: [UserTable.id]
    }),
    manager: one(UserTable, {
        fields: [CTable.manager_Id],
        references: [UserTable.id]
    }),
    As: one(ATable, {
        fields: [CTable.year, CTable.week, CTable.ownerId],
        references: [ATable.year, ATable.week, ATable.ownerId]
    })
}))

export const BTable = pgTable('b', {
    id: serial('id').primaryKey(),

    // Foreign Key
    year: smallint('year').notNull(),
    week: smallint('week').notNull(),
    ownerId: text('ownerId').notNull(),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
}, (table) => {
    return {
        unq: unique().on(table.year, table.week, table.ownerId, table.project_Id)
    }
});

export const BRelations = relations(BTable, ({ one }) => ({
    As: one(ATable, {
        fields: [BTable.year, BTable.week, BTable.ownerId],
        references: [ATable.year, ATable.week, ATable.ownerId]
    }),
}));

export const CTable = pgTable('C', {
    id: serial('id').primaryKey(),
    who_Id: text('whoId').references(() => UserTable.id),
    manager_Id: text('managerId').references(() => UserTable.id),
    status: StatusEnum('status').notNull(),

    // Foreign Key
    year: smallint('year'),
    week: smallint('week'),
    ownerId: text('ownerId'),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
});

export const CRelations = relations(CTable, ({ one }) => ({
    who: one(UserTable, {
        fields: [CTable.who_Id],
        references: [UserTable.id]
    }),
    manager: one(UserTable, {
        fields: [CTable.manager_Id],
        references: [UserTable.id]
    }),
    As: one(ATable, {
        fields: [CTable.year, CTable.week, CTable.ownerId],
        references: [ATable.year, ATable.week, ATable.ownerId]
    })
}))

ArwanceR•2y ago

how is UserTable.id declared?

Gaming

Programming

Cannot query DB table get `COALESCE types smallint and text cannot be matched`