Drizzle Team•15mo ago

Trying to get relations from one table to another, as one-to-many but get only one value

I have a one-to-many relation on a table, and I want to get all the workoutResults associated with a workoutSession. My query looks like this:

      const qb = db
        .select()
        .from(workoutSession)
        // .leftJoin(event, eq(event.id, workoutSession.eventId));
        .leftJoin(
          workoutResult,
          eq(workoutSession.id, workoutResult.workoutSessionId)
        )

      const qb = db
        .select()
        .from(workoutSession)
        // .leftJoin(event, eq(event.id, workoutSession.eventId));
        .leftJoin(
          workoutResult,
          eq(workoutSession.id, workoutResult.workoutSessionId)
        )

I don't understand why workoutSession.workoutResult is not an array, since there are many workoutResults with the same workoutSessionId I hope I'm clear enough. I've also tried adding the .all() at the end of the query, but this does not seem to exist anywhere in drizzle 🤔

12 Replies

Foxtrot•15mo ago

same issue. 😦

Angelelz•15mo ago

Can you show how are you defining your tables and relations?

Foxtrot•15mo ago

Thanks for your response. User Table:

export const users = pgTable("users", {
    id: serial("id").primaryKey(),
    ...
    instituteId: serial("institute_id")
        .notNull()
        .references(() => institutes.id)
});

export const usersRelations = relations(users, ({ many, one }) => ({
    addresses: many(addresses),
    institute: one(institutes, {
        fields: [users.instituteId],
        references: [institutes.id],
    }),
}));

export const users = pgTable("users", {
    id: serial("id").primaryKey(),
    ...
    instituteId: serial("institute_id")
        .notNull()
        .references(() => institutes.id)
});

export const usersRelations = relations(users, ({ many, one }) => ({
    addresses: many(addresses),
    institute: one(institutes, {
        fields: [users.instituteId],
        references: [institutes.id],
    }),
}));

Address Table:

export const addresses = pgTable("addresses", {
    id: serial("id").primaryKey(),
    type: addressTypeEnum("type").notNull(),
    street: varchar("street", { length: 255 }).notNull(),
    ...
    userId: serial("user_id").references(() => users.id)
});

export const addressesRelations = relations(addresses, ({ one }) => ({
    user: one(users, {
        fields: [addresses.userId],
        references: [users.id],
    }),
}));

export const addresses = pgTable("addresses", {
    id: serial("id").primaryKey(),
    type: addressTypeEnum("type").notNull(),
    street: varchar("street", { length: 255 }).notNull(),
    ...
    userId: serial("user_id").references(() => users.id)
});

export const addressesRelations = relations(addresses, ({ one }) => ({
    user: one(users, {
        fields: [addresses.userId],
        references: [users.id],
    }),
}));

Fetching data:

const user = await db
                .select()
                .from(users)
                .innerJoin(addresses, eq(users.id, addresses.userId))
                .where(eq(users.id, id));

const user = await db
                .select()
                .from(users)
                .innerJoin(addresses, eq(users.id, addresses.userId))
                .where(eq(users.id, id));

Angelelz•15mo ago

When you do an inner join, the shape of the result will be the shape that the driver returns. Drizzle won't do any data transformation. The query that you showed, will return an array of objects with all the columns from users and addresses all together. The crud API is designed to be transparent from SQL -> JS If you want a user with an array of addresses, I would suggest you use the relations API

Angelelz•15mo ago

https://orm.drizzle.team/docs/rqb#find-first

Drizzle Queries - DrizzleORM

Drizzle ORM | %s

Foxtrot•15mo ago

If i go with this approach i am getting the following error: "Cannot read properties of undefined (reading 'referencedTable')" am i missing something that i didn't noticed? Schema is as above i mentioned earlier.

const user = await db.query.users.findFirst({
                where: (user, { eq }) => eq(user.id, id),
                with: {
                    addresses: true,
                },
            });

const user = await db.query.users.findFirst({
                where: (user, { eq }) => eq(user.id, id),
                with: {
                    addresses: true,
                },
            });

Angelelz•15mo ago

Are you passing your schema to the drizzle object when defined? In the examples, they always pass the schema to the drizzle function

Foxtrot•15mo ago

Yes,

import constant from "@/utils/constant";
import * as schemas from "@database/schema";
import "dotenv/config";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const client = postgres({
    database: constant().DB_NAME,
    host: constant().DB_HOST,
    port: constant().DB_PORT,
    password: constant().DB_PASSWORD,
    user: constant().DB_USER,
});

export default drizzle(client, { schema: schemas });

import constant from "@/utils/constant";
import * as schemas from "@database/schema";
import "dotenv/config";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const client = postgres({
    database: constant().DB_NAME,
    host: constant().DB_HOST,
    port: constant().DB_PORT,
    password: constant().DB_PASSWORD,
    user: constant().DB_USER,
});

export default drizzle(client, { schema: schemas });

@databse/schema

export { addresses } from "./addresses";
export { institutes } from "./institutes";
export { users } from "./users";

export { addresses } from "./addresses";
export { institutes } from "./institutes";
export { users } from "./users";

Angelelz•15mo ago

All of that seems ok Do you have a reproduction repo?

Foxtrot•15mo ago

found the error. i didnt export the relation schema

export { addresses, addressesRelations } from "./addresses";
export { institutes } from "./institutes";
export { users, usersRelations } from "./users";

export { addresses, addressesRelations } from "./addresses";
export { institutes } from "./institutes";
export { users, usersRelations } from "./users";

it's working @angelelz Thanks for your valuable time. I appreciate your help ❤️ .

Mr.T 🐻⛓OP•15mo ago

This works fine, for me it was the relation in the schema that wasn't well made after running introspect. A side issue I get from using queries now, is the fact that I don't see how you can chain where using that method.. Any example of using multiple where in accessing data using queries? Thanks a lot!

Foxtrot•15mo ago

i think you can use 'and' operator.

Gaming

Programming

Trying to get relations from one table to another, as one-to-many but get only one value