Is it possible to pass sub query to `from`?

I have a db.select().from() and I need to pass a nested query into that from.

        SELECT g.id, g.name, g.background_image, json_agg(p) as platforms
        FROM (
            SELECT g.*
            FROM games g
            JOIN games_to_platforms gtp on gtp.game_id = g.id
            JOIN platforms p on gtp.platform_id = p.id
            WHERE p.slug = 'pc'
        ) as g

        SELECT g.id, g.name, g.background_image, json_agg(p) as platforms
        FROM (
            SELECT g.*
            FROM games g
            JOIN games_to_platforms gtp on gtp.game_id = g.id
            JOIN platforms p on gtp.platform_id = p.id
            WHERE p.slug = 'pc'
        ) as g

2 Replies

Angelelz•11mo ago

Yes, check here: https://orm.drizzle.team/docs/select#select-from-subquery

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

bambam22OP•11mo ago

Thank youuuuuu Not sure what's going on here, this is my subquery but the .from won't accept it

        const g = db
            .select({
                ...getTableColumns(GamesTable),
            })
            .from(GamesTable)
            .innerJoin(GamesToPlatforms, eq(GamesTable.id, GamesToPlatforms.gameId))jj
        if (query.platformSlug) {
            g.where(eq(PlatformsTable.slug, query.platformSlug))
        }
        g.as('g')

        const g = db
            .select({
                ...getTableColumns(GamesTable),
            })
            .from(GamesTable)
            .innerJoin(GamesToPlatforms, eq(GamesTable.id, GamesToPlatforms.gameId))jj
        if (query.platformSlug) {
            g.where(eq(PlatformsTable.slug, query.platformSlug))
        }
        g.as('g')

        const statement = db
            .select({
                ...getTableColumns(GamesTable),
                platforms: sql<
                    Platform[]
                >`json_agg(json_build_object('id', platforms.id, 'name', platforms.name, 'slug', platforms.slug, 'parentSlug', platforms.parent_slug, 'gamesCount', platforms.games_count, 'imageBackground', platforms.image_background))`,
            })
            .from(g)

        const statement = db
            .select({
                ...getTableColumns(GamesTable),
                platforms: sql<
                    Platform[]
                >`json_agg(json_build_object('id', platforms.id, 'name', platforms.name, 'slug', platforms.slug, 'parentSlug', platforms.parent_slug, 'gamesCount', platforms.games_count, 'imageBackground', platforms.image_background))`,
            })
            .from(g)

The type is not assignable Ignore this

Gaming

Programming

Is it possible to pass sub query to `from`?