Wabathur
Wabathur
PostsComments
DTDrizzle Team
Created by Wabathur on 12/10/2023 in #help
[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
5 replies