DT
Drizzle Team•6d ago
Bryce

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.
5 Replies
TOSL
TOSL•5d ago
You defined userRelations twice id column on userTable is char createdBy is varchar
Bryce
BryceOP•5d ago
Good catch. I've fixed those issues but I'm still getting the same error
TOSL
TOSL•5d ago
So what is schema now? repost please And just to be sure you have pushed/migrated these changes to you db
Bryce
BryceOP•5d ago
The original post has been updated with the corrected schemas Here is the raw SQL generated by the query. Executing the SQL directly on the database results in the same error 
select `id`, `title`, `createdBy`, 
(
    select json_array(`userID`, `email`, `name`, `createdAt`, `updatedAt`) 
    from (
        select * from `users` `testTable_creator` 
        where `testTable_creator`.`userID` = `testTable`.`createdBy` 
        limit 1
    ) `testTable_creator`
) as `creator` 
from `test` `testTable`
select `id`, `title`, `createdBy`, 
(
    select json_array(`userID`, `email`, `name`, `createdAt`, `updatedAt`) 
    from (
        select * from `users` `testTable_creator` 
        where `testTable_creator`.`userID` = `testTable`.`createdBy` 
        limit 1
    ) `testTable_creator`
) as `creator` 
from `test` `testTable`
adrtivv
adrtivv•5d ago
@Bryce try providing the same value for relationName field on both many and one functions like this: 
many(testTable, { relationName: "someUniqueNameHere" })
many(testTable, { relationName: "someUniqueNameHere" })
one(usersTable, { fields: [testTable.createdBy], references: [usersTable.id], relationName: "someUniqueNameHere" }
one(usersTable, { fields: [testTable.createdBy], references: [usersTable.id], relationName: "someUniqueNameHere" }
generate and apply the migration and then try again

Did you find this page helpful?