Drizzle Team•17mo ago

How to Chain multiple selects

I am new to ORMS and SQL so please bear with me. I have the attached schema for the chat component of the app that I am building. I am a bit lost how I would chain this sequence of select statements

select * from chat_room_participants where user_uid='d0fc7e46-a8a5-4fd4-8ba7-af485013e6fa'
select * from chat_rooms where id=1
select * from chat_messages where chat_room_id=1

select * from chat_room_participants where user_uid='d0fc7e46-a8a5-4fd4-8ba7-af485013e6fa'
select * from chat_rooms where id=1
select * from chat_messages where chat_room_id=1

help much appreciated

19 Replies

venOP•17mo ago

here is my query

        const data: any = await db.query.chatMessages.findMany({
            where: eq(chatMessages.userUid, query.user_uid),
            with: {
                // chatRoomParticipants: true,
                chatRooms: true,
            },
        });

        const data: any = await db.query.chatMessages.findMany({
            where: eq(chatMessages.userUid, query.user_uid),
            with: {
                // chatRoomParticipants: true,
                chatRooms: true,
            },
        });

i am getting this error

error Error: Relation chatRooms not found                                                                                   2:49:16 PM  
    at PgDialect.buildRelationalQuery (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/alias-3e926a50.mjs:1032:23)
    at file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/session-2062e9e6.mjs:285:40
    at Object.startActiveSpan (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/alias-3e926a50.mjs:644:20)
    at QueryPromise._prepare (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/session-2062e9e6.mjs:284:23)
    at file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/session-2062e9e6.mjs:301:25
    at Object.startActiveSpan (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/alias-3e926a50.mjs:644:20)
    at QueryPromise.execute (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/session-2062e9e6.mjs:300:23)
    at QueryPromise.then (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/alias-3e926a50.mjs:636:21)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

error Error: Relation chatRooms not found                                                                                   2:49:16 PM  
    at PgDialect.buildRelationalQuery (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/alias-3e926a50.mjs:1032:23)
    at file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/session-2062e9e6.mjs:285:40
    at Object.startActiveSpan (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/alias-3e926a50.mjs:644:20)
    at QueryPromise._prepare (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/session-2062e9e6.mjs:284:23)
    at file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/session-2062e9e6.mjs:301:25
    at Object.startActiveSpan (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/alias-3e926a50.mjs:644:20)
    at QueryPromise.execute (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/session-2062e9e6.mjs:300:23)
    at QueryPromise.then (file:///C:/Users/Ven/OneDrive/Documents/GitHub/project-juniper/App/node_modules/drizzle-orm/alias-3e926a50.mjs:636:21)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

MAST•17mo ago

Did you define the relations in drizzle orm

MAST•17mo ago

https://orm.drizzle.team/docs/rqb

Relational queries – DrizzleORM

Drizzle ORM | %s

venOP•17mo ago

so its not like Prisma and infer the relations @mast1999 ? would i do this in a seperate file? because what happens when i have to regenerate the schema file through introspection? I am mainly using drizzle for introspection and not for migrations

MAST•17mo ago

Yes, currently in drizzle you have to define the relations between the tables yourself if you want to use the relational queries. Introspection doesn't generate them.

MAST•17mo ago

The other option is to use joins to get the same result but it's more work. https://orm.drizzle.team/docs/joins

Joins [SQL] – DrizzleORM

Drizzle ORM | %s

venOP•17mo ago

i decided to use joins because i couldnt figure out how to setup the relationships post introspection. i wish the documentation was a bit more clear.

MAST•17mo ago

It can be kind of confusing 😄 Which part is hard to understand? Maybe I can give you a hand?

venOP•17mo ago

this is my schema for the chat portion of my app

venOP•17mo ago

i ended up with a left join

        const data = await db
            .select()
            .from(chatRooms)
            .leftJoin(
                chatRoomParticipants,
                eq(chatRooms.id, chatRoomParticipants.chatRoomId)
            )
            .leftJoin(
                profile,
                eq(profile.userUid, chatRoomParticipants.userUid)
            )
            .where(eq(chatRooms.ownerUid, userId));

        const data = await db
            .select()
            .from(chatRooms)
            .leftJoin(
                chatRoomParticipants,
                eq(chatRooms.id, chatRoomParticipants.chatRoomId)
            )
            .leftJoin(
                profile,
                eq(profile.userUid, chatRoomParticipants.userUid)
            )
            .where(eq(chatRooms.ownerUid, userId));

@mast1999 i tried all kinds of stuff to get db.query work finally threw in the towel

MAST•17mo ago

It's kind of hard to explain here, want to jump on a call? But in short each table need a relation and in that relation you should define what kind of relation that table has to the other tables. For example:

export const chatMessageRelations = relations(chatMessage, ({ many, one }) => ({
  room: one(chatRoom, {
    fields: [chatMessage.chatRoomId],
    references: [chatRoom.id],
  }),
}));
export const chatRoomRelations = relations(chatRoom, ({ many, one }) => ({
  room: many(chatMessage),
}));

export const chatMessageRelations = relations(chatMessage, ({ many, one }) => ({
  room: one(chatRoom, {
    fields: [chatMessage.chatRoomId],
    references: [chatRoom.id],
  }),
}));
export const chatRoomRelations = relations(chatRoom, ({ many, one }) => ({
  room: many(chatMessage),
}));

The Fields are the columns on the table that you are defining the relation for and the references is well the columns on the table it is referencing 😄

MAST•17mo ago

This for example is a one to many relationship. many to many relationships are more complicated because you have to define a middle table that stores a reference from each table in there. And then define a one to one relationship in the relations of the main tables. Similar to the docs: https://orm.drizzle.team/docs/rqb#many-to-many

Relational queries – DrizzleORM

Drizzle ORM | %s

MAST•17mo ago

BTW after defining the you need to pass the schemas to the drizzle instance when you are creating it. https://orm.drizzle.team/docs/rqb#querying

Relational queries – DrizzleORM

Drizzle ORM | %s

venOP•17mo ago

i can jump on a call. are you free now?

MAST•17mo ago

Yep I'm free now

venOP•17mo ago

i dont know know how to start a call 🙂 new to discord

MAST•17mo ago

venOP•17mo ago

i missed the one part because the example only give many hang on. let me figure out this call stuff

MAST•17mo ago

You can right click on my image and the it should say call as one of the options

Gaming

Programming

How to Chain multiple selects