Partial Select - Child array
I have a one-many relationship of recipe->comments. How do I get it to return the comments as an array?
const test = await db
.select({
...getTableColumns(recipe),
comments: getTableColumns(comment), // This should be an array
})
.from(recipe)
.leftJoin(comment, eq(recipe.id, comment.recipeId));
3 Replies
Hi, @scriptbytes! You can achieve this by using relational queries
https://orm.drizzle.team/docs/rqb
If you use query builder like now, you have to map result by yourself, because this is how database return rows.
Drizzle ORM - next gen TypeScript ORM
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
alternatively, https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15 will let you do this via a
jsonAgg function from join queriesYea I saw I was able to do that, but my next issue is it seems there's things I can with the select syntax that I can't do in the query syntax.
For example, let's say in my query I want to also select a field that is the Count of likes:
const test = await db
.select({
...getTableColumns(recipe),
comments: getTableColumns(comment), // This should be an array
likeCount:
sql<number>
(select cast(count(*) as int) from ${recipeLike} where ${recipeLike.recipeId} = ${recipe.id}).as(
'like_count'
)
})
.from(recipe)
.leftJoin(comment, eq(recipe.id, comment.recipeId));
After some tinkering I got my sub-query count to work, but I had to hard-code the name of the table and the column name in the where that I'm querying from, otherwise it uses the wrong name:
extras: {
likeCount:
sql(select cast(count(*) as int) from recipe_like where recipe_like.recipe_id = ${recipe.id}).as(
'lowered_name'
),
},