3 ms·

How would you add also the count of the votes of each comment in the aggregation, as per the example in the article?

deleted 4y ago
[deleted]
simonw 4y ago
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=https://gist.githubusercontent.com/simonw/3d6cbcd55beda108a88265a80f042726/raw/06ac070945874bdfe78442973ae76240cd8de370/posts_comments_votes.sql#/data?sql=with+comment_vote_counts+as+%28%0A++select%0A++++comment_id%2C%0A++++count%28*%29+as+vote_count%0A++from%0A++++votes%0A++group+by%0A++++comment_id%0A%29%2C%0Acomments_with_vote_counts+as+%28%0A++select%0A++++id%2C%0A++++post_id%2C%0A++++content%2C%0A++++coalesce%28vote_count%2C+0%29+as+votes%0A++from%0A++++comments%0A++++left+join+comment_vote_counts+on+comments.id+%3D+comment_vote_counts.comment_id%0A%29%0Aselect%0A++posts.id%2C%0A++posts.title%2C%0A++posts.content%2C%0A++json_group_array%28%0A++++json_object%28%0A++++++%27id%27%2C%0A++++++comments_with_vote_counts.id%2C%0A++++++%27content%27%2C%0A++++++comments_with_vote_counts.content%2C%0A++++++%27votes%27%2C%0A++++++comments_with_vote_counts.votes%0A++++%29%0A++%29+as+comments%0Afrom%0A++posts%0A++join+comments_with_vote_counts+on+comments_with_vote_counts.post_id+%3D+posts.id%0A++group+by+posts.id 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 4y ago
  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

deleted 4y ago

[deleted]

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=https://gist.githubusercontent.com/simonw/3d6cbcd55beda108a88265a80f042726/raw/06ac070945874bdfe78442973ae76240cd8de370/posts_comments_votes.sql#/data?sql=with+comment_vote_counts+as+%28%0A++select%0A++++comment_id%2C%0A++++count%28*%29+as+vote_count%0A++from%0A++++votes%0A++group+by%0A++++comment_id%0A%29%2C%0Acomments_with_vote_counts+as+%28%0A++select%0A++++id%2C%0A++++post_id%2C%0A++++content%2C%0A++++coalesce%28vote_count%2C+0%29+as+votes%0A++from%0A++++comments%0A++++left+join+comment_vote_counts+on+comments.id+%3D+comment_vote_counts.comment_id%0A%29%0Aselect%0A++posts.id%2C%0A++posts.title%2C%0A++posts.content%2C%0A++json_group_array%28%0A++++json_object%28%0A++++++%27id%27%2C%0A++++++comments_with_vote_counts.id%2C%0A++++++%27content%27%2C%0A++++++comments_with_vote_counts.content%2C%0A++++++%27votes%27%2C%0A++++++comments_with_vote_counts.votes%0A++++%29%0A++%29+as+comments%0Afrom%0A++posts%0A++join+comments_with_vote_counts+on+comments_with_vote_counts.post_id+%3D+posts.id%0A++group+by+posts.id 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 4y ago
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