[pg] How do I make subquery in select()?

I wanna write a query like this in drizzle, but it doesn't seem like working

SELECT
    books.*,
    (
        SELECT jsonb_build_object('genres', json_agg(jsonb_build_object(
            'id', genres.id,
            'name', genres.name,
            -- Add other columns as needed
        )))
        FROM genres
        JOIN toons_genres ON genres.id = toons_genres.genre_id
        WHERE toons_genres.toon_id = toons.id
    ) AS matched_genres

SELECT
    books.*,
    (
        SELECT jsonb_build_object('genres', json_agg(jsonb_build_object(
            'id', genres.id,
            'name', genres.name,
            -- Add other columns as needed
        )))
        FROM genres
        JOIN toons_genres ON genres.id = toons_genres.genre_id
        WHERE toons_genres.toon_id = toons.id
    ) AS matched_genres

3 Replies

energistic•10mo ago

Maybe use with? https://orm.drizzle.team/docs/select/#with-clause

Drizzle ORM - next gen TypeScript ORM

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

Dry_Luke•10mo ago

with indeed works but is there no other way to do it like @Wabathur mentioned? (just curious)

Angelelz•10mo ago

Yes it's a 1 to 1 change but you have to use the sql operator

db.select({
  ...getTableColumns(books), 
  genres: sql`jsonb_build_object('genres', json_agg(jsonb_build_object('id', ${genres.id} ... )))`
})
...

db.select({
  ...getTableColumns(books), 
  genres: sql`jsonb_build_object('genres', json_agg(jsonb_build_object('id', ${genres.id} ... )))`
})
...

Gaming

Programming

[pg] How do I make subquery in select()?