How would you add also the count of the votes of each comment in the aggregation, as per the example in the article?
with comment_vote_counts as (
select
comment_id,
count(*) as vote_count
from
votes
group by
comment_id
),
comments_with_vote_counts as (
select
id,
post_id,
content,
coalesce(vote_count, 0) as votes
from
comments
left join comment_vote_counts on comments.id = comment_vote_counts.comment_id
)
select
posts.id,
posts.title,
posts.content,
json_group_array(
json_object(
'id',
comments_with_vote_counts.id,
'content',
comments_with_vote_counts.content,
'votes',
comments_with_vote_counts.votes
)
) as comments
from
posts
join comments_with_vote_counts on comments_with_vote_counts.post_id = posts.id
group by posts.idI rewrote a lot of ORM stuff into sql queries, DRY'd up a lot of the queries into parameterized CTEs so the DB engine could cache and optimize, generating arrays and JSON data (thanks once again, Postgres), then wrote stored procs to handle optional parameters that could then be called by the APIs again. Magnitudes of difference in performance