lewisedc
lewisedc
PostsComments
DTDrizzle Team
Created by lewisedc on 1/7/2025 in #help
Is there any way to have a subquery in an insert statement?
I don't really understand why, but that seems to work, thank you very much!
4 replies
DTDrizzle Team
Created by lewisedc on 12/3/2024 in #help
What's the recommended way to handle joins that aggregate results?
Thanks for letting me know! I had an obsession in the past with trying to write sql queries that got all the data and formatted it exactly how I needed it so I'm going to be writing a lot of data formatters, but still loving Drizzle haha 😄
6 replies
DTDrizzle Team
Created by lewisedc on 12/3/2024 in #help
What's the recommended way to handle joins that aggregate results?
bump
6 replies
DTDrizzle Team
Created by lewisedc on 12/3/2024 in #help
What's the recommended way to handle joins that aggregate results?
One solution I came up with is this: 
db
    .select({
      name: gamesTable.name,
      imageUrl: gamesTable.imageUrl,
      features: sql`JSON_AGG(JSON_BUILD_OBJECT('name', ${gameFeaturesTable.name}, 'icon_url', ${gameFeaturesTable.iconUrl}))`,
    })
    .from(gamesTable)
    .leftJoin(gameFeaturesTable, eq(gameFeaturesTable.id, gamesToGameFeaturesTable.gameFeatureId))
    .groupBy(gamesTable.name, gamesTable.imageUrl, gamesTable.sortOrder)
    .orderBy(gamesTable.sortOrder);
db
    .select({
      name: gamesTable.name,
      imageUrl: gamesTable.imageUrl,
      features: sql`JSON_AGG(JSON_BUILD_OBJECT('name', ${gameFeaturesTable.name}, 'icon_url', ${gameFeaturesTable.iconUrl}))`,
    })
    .from(gamesTable)
    .leftJoin(gameFeaturesTable, eq(gameFeaturesTable.id, gamesToGameFeaturesTable.gameFeatureId))
    .groupBy(gamesTable.name, gamesTable.imageUrl, gamesTable.sortOrder)
    .orderBy(gamesTable.sortOrder);
However this has the downside of not being strongly typed as the features column of the result is now type unknown. Another way I can think of is just doing the joins normally and then using javascript to actual format it how I want. Would appriciate peoples take on this!
6 replies
DTDrizzle Team
Created by lewisedc on 11/21/2024 in #help
Is this query possible using the Query API?
Sorry for late reply, didn't realise anyone replied to me haha, used your example of a subquery and it worked great, thanks for the help!
6 replies