DT
Drizzle Team•13mo ago
bambam22

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
Angelelz•13mo 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.
bambam22
bambam22OP•13mo 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

Did you find this page helpful?