4 ms·
There's another option with many databases these days: you can often use aggregation functions to return the related data as part of a single query, even across
by simonw 4y ago
There's another option with many databases these days: you can often use aggregation functions to return the related data as part of a single query, even across many-to-many tables.
I wrote up how to do that using JSON aggregation functions in both SQLite and PostgreSQL for example: https://til.simonwillison.net/sqlite/related-rows-single-query https://til.simonwillison.net/sqlite/related-rows-single-que...
- panzerboiler 4y agoHow 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 agoLots 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 agoI'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