[simonw]

Lots of ways to do that, one way would be using a CTE like this one: https://lite.datasette.io/?install=datasette-pretty-json&sql...

    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.id

[xarope]

I've done something similar. Legacy system in VB, ported to Csharp, moved to client-server rather than monolithic platform, and performance tanked (100s of queries local vs same number of queries over a network, and hence latency added 100s of ms to each "transaction").

I 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