6 ms·
One 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 t
by rurabe 4y ago
One 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/#...
- chc 4y agoPersonally, I find the Rails version a bit more elegant because it is declarative, reusable and composable, while Django's approach is a more utilitarian "just write the dang query when you want it." But both are a great illustration of how a well-designed ORM can give you the tools you need to get good performance.
- saila 4y agoYou can do the same kind of thing in Django. The approach is a little different, but it's the same basic idea. https://docs.djangoproject.com/en/4.1/topics/db/managers/#custom-managers https://docs.djangoproject.com/en/4.1/topics/db/managers/#cu...