4 ms·
You could probably get this down to two queries, one for posts and one for comments, if you aggregate the vote count when retrieving the comments. I think this
by saila 4y ago
You could probably get this down to two queries, one for posts and one for comments, if you aggregate the vote count when retrieving the comments. I think this is pretty easy to do with most ORMs.
You could also get it down to 1 query using SQL. This is one way to do it based on the schema in the article [postgres, not well tested]:
with
latest_posts as (
select * from post limit 3
),
latest_comments as (
select
c.*, count(v.id) as votes
from
comment c
left join
vote v on v.comment_id = c.id
where
c.post_id in (select id from latest_posts)
group by
c.id, c.content
)
select
p.*, json_agg(c)
from
latest_posts p
left join
latest_comments c on c.post_id = p.id
group by
p.id, p.title, p.content
# NOTE: fixed SQL bug noted by @rurabe
Off the top of my head, I'm not sure how you would (or if you could) do this with ActiveRecord, SQLAlchemy, or the Django ORM, but it's probably more complicated than just writing the SQL.
To be clear, I'm not anti-ORM and use them all the time, but it really helps to understand SQL well when using them and to know when it's appropriate to switch to SQL.
- gnuvince 4y ago> To be clear, I'm not anti-ORM and use them all the time, but it really helps to understand SQL well when using them and to know when it's appropriate to switch to SQL. When I did web development, I saw it as a "hack" and a "failure to write clean code" whenever I reached for raw SQL. This is of course not true at all, but it was a powerful psychological blocker and I'd spend too much time trying to figure how to get the ORM to do what I wanted instead of writing the SQL myself and moving on to the next problem.
- joshuahedlund 4y ago> but it really helps to understand SQL well when using them and to know when it's appropriate to switch to SQL. I agree. I often feel like I benefited by starting my web career pre-ORM and only learning to use them a few years in, so I can appreciate and use both. I sometimes wonder if it’s harder for new devs to acquire the same kind of experience.
- pphysch 4y agoIt's pretty straightforward in Django. The key is being comfortable with writing custom Manager/QuerySet methods. You could do something like `Post.objects.latest().annotate_comments()` which would resolve almost exactly to the query you wrote above.
- saila 4y agoUsing a typical set of Post & Comment models, where Comment has a foreign key to Post, I couldn't figure out how to do this with just a single query in Django. Using prefetch_related, the 2-query version is pretty straightforward: from django.db.models import Count, Prefetch from myproject.models import Post, Comment # This will fetch the 3 posts first and then the comments for those posts query = Post.objects.prefetch_related( Prefetch("comments", queryset=Comment.objects.annotate(Count("votes"))) ) posts = query[:3] How would you reduce this to one query using the Django ORM?
- pphysch 4y agoThe previous query uses a JsonAgg to collect the comments
- deleted 4y ago[deleted]
- rurabe 4y agoOne neat trick that I think is relatively lesser known is that you can select arbitrary sql expressions in ActiveRecord and those values are made available on the instances. (Also I think the above sql needs to be tweaked since you need the votes count grouped by comment not by post) A one to many relationship in pure SQL is an awkward fit with a Rails app as it requires serializing (at least) the many as json. Then there's this weird conceptual gotcha where one resource is an AR instance and another is a pure hash. I'd probably make a scope and association to help out here: class Comment scope :with_vote_count, ->{ joins(:votes).select('comments.*').select('count(votes.*) as vote_count') } end class Post has_many :comments has_many :comments_with_vote_counts, ->{ with_vote_counts }, class_name: 'Comment' end # in controller @posts = Post.includes(:comments_with_vote_counts).limit(3).order(:created_at: :desc) # in view/serializer, posts and comments are both AR instances @posts.each do |post| post.comments.each do |comment| comment.vote_count # => Integer end end This should give you 2 queries, one to load the posts, then one to load the comments and vote counts for the relevant posts. Controller stays nice and slim and the complexity is delegated to sql via the join scope, without any other dependencies. * edited for HN code block syntax
- VWWHFSfQ 4y agoDjango will do something similar (possibly a little more elegantly) if one is familiar with how to use the Prefetch APIs [1]: Post.objects.order_by("-created_at").prefetch_related( Prefetch( "comments", queryset=Comment.objects.annotate( vote_count=Count("votes") ), ) )[:3] This will generate the following two queries: SELECT "post"."id", "post"."created_at", "post"."title", "post"."content" FROM "post" ORDER BY "post"."created_at" DESC LIMIT 3; SELECT "comment"."id", "comment"."post_id", "comment"."content", COUNT("vote"."id") AS "vote_count" FROM "comment" LEFT OUTER JOIN "vote" ON ("comment"."id" = "vote"."comment_id") WHERE "comment"."post_id" IN (3, 2, 1) GROUP BY "comment"."id", "comment"."post_id" [1] https://docs.djangoproject.com/en/4.1/ref/models/querysets/#django.db.models.Prefetch https://docs.djangoproject.com/en/4.1/ref/models/querysets/#...