Drizzle ORM How do I include one side of a many to many as an array of objects in a select many?

Question

I have three tables with a many to many relationship, Games, GamesToPlatforms, Platforms. How do I query all Games so that it has a key of platforms that is an array of platform objects associated to it threw the join table.

const GamesTable = pgTable(
    'games',
    {
        id: uuid('id').primaryKey().defaultRandom().notNull(),
        name: varchar('name', { length: 255 }).notNull(),
        backgroundImage: text('background_image').notNull(),
    })

const GamesRelations = relations(GamesTable, ({ one, many }) => ({
    platforms: many(GamesToPlatformsTable)
})

const GamesToPlatformsTable = pgTable(
    'games_to_platforms',
    {
        gameId: uuid('game_id').notNull(),
        platformId: smallint('platform_id').notNull(),
    },
    (t) => {
        return {
            uniqueIdx: uniqueIndex(`unique_idx`).on(t.gameId, t.platformId),
        }
    }
)

const GamesToPlatformsRelations = relations(
    GamesToPlatformsTable,
    ({ one }) => {
        return {
            platform: one(PlatformsTable, {
                fields: [GamesToPlatformsTable.platformId],
                references: [PlatformsTable.id],
            }),
    })

const PlatformsTable = pgTable(
    'platforms',
    {
        id: smallint('id').primaryKey().notNull(),
        name: varchar('name', { length: 255 }).notNull(),
        imageBackground: text('image_background').notNull(),
    },
    (platforms) => {
        return {
            uniqueIdx: uniqueIndex(`unique_idx`).on(platforms.slug),
        }
    }
)

const PlatformsRelations = relations(PlatformsTable, ({ many }) => {
    return {
        games: many(GamesToPlatformsTable),
    }
})

Answer 1

You can do it like this:

const result: Response = await db.query.GamesTable.findMany({
    with: {
        platforms: {
            columns: {},
            with: {
                platform: true
            }
        }
    }
})

You will get an array of this shape:

type Response = {
    id: string;
    name: string;
    backgroundImage: string;
    platforms: {
        platform: {
            id: number;
            name: string;
            imageBackground: string;
        };
    }[];
}[]

You will still need to access the junction table and then the platform property inside each object because, for now, there is no other way to do it in Drizzle ORM's Relational Queries API. You can always map it to any shape you need, but I would recommend using it as it is