Bryce Posts - Answer Overflow

Bryce

•Created by Bryce on 12/31/2024 in #help

Help with relations - Error: Unknown column in 'where clause'

When I try run this query to fetch all 'test' records along with the users that created each record, I get the following error:

const result = await db.query.testTable.findMany({
    with: {
        creator: true
    }
});

const result = await db.query.testTable.findMany({
    with: {
        creator: true
    }
});

Error: Unknown column 'testTable.createdBy' in 'where clause'

Error: Unknown column 'testTable.createdBy' in 'where clause'

Here are the schemas for testTable and usersTable:

import { relations } from "drizzle-orm";
import { char, mysqlTable, varchar } from "drizzle-orm/mysql-core";
import { usersTable } from "./users.schema";

export const testTable = mysqlTable('test', {
    id: varchar('id', { length: 40}).primaryKey(),
    title: varchar('title', { length: 100 }).notNull(),
    createdBy: char('createdBy', ({ length: 32 })).references(() => usersTable.id),
});

export const testRelations = relations(testTable, ({ one }) => ({
    creator: one(usersTable, { fields: [testTable.createdBy], references: [usersTable.id] }),
}));

import { relations } from "drizzle-orm";
import { char, mysqlTable, varchar } from "drizzle-orm/mysql-core";
import { usersTable } from "./users.schema";

export const testTable = mysqlTable('test', {
    id: varchar('id', { length: 40}).primaryKey(),
    title: varchar('title', { length: 100 }).notNull(),
    createdBy: char('createdBy', ({ length: 32 })).references(() => usersTable.id),
});

export const testRelations = relations(testTable, ({ one }) => ({
    creator: one(usersTable, { fields: [testTable.createdBy], references: [usersTable.id] }),
}));

import { relations, sql } from "drizzle-orm";
import { char, mysqlTable, timestamp, varchar } from "drizzle-orm/mysql-core";
import { testTable } from "./test.schema";

export const usersTable = mysqlTable('users', {
    id: char('userID', { length: 32 }).primaryKey(),
    email: varchar('email', { length: 45 }).notNull(),
    name: varchar('name', { length: 45 }).notNull(),
    createdAt: timestamp('createdAt', { mode: 'date' }).default(sql`CURRENT_TIMESTAMP`),
    updatedAt: timestamp('updatedAt', { mode: 'date' }).default(sql`CURRENT_TIMESTAMP`)
});

export const userRelations = relations(usersTable, ({ one, many }) => ({
    tests: many(testTable),
}));

import { relations, sql } from "drizzle-orm";
import { char, mysqlTable, timestamp, varchar } from "drizzle-orm/mysql-core";
import { testTable } from "./test.schema";

export const usersTable = mysqlTable('users', {
    id: char('userID', { length: 32 }).primaryKey(),
    email: varchar('email', { length: 45 }).notNull(),
    name: varchar('name', { length: 45 }).notNull(),
    createdAt: timestamp('createdAt', { mode: 'date' }).default(sql`CURRENT_TIMESTAMP`),
    updatedAt: timestamp('updatedAt', { mode: 'date' }).default(sql`CURRENT_TIMESTAMP`)
});

export const userRelations = relations(usersTable, ({ one, many }) => ({
    tests: many(testTable),
}));

The issue seems to be because the generated SQL query contains a subquery that references a table in the outer query. Any ideas would be appreciated.

10 replies

Gaming

Programming