I have model Issue that has_many :comments, model Comment that has_many :assets, as: :assetable, class_name: 'Comment::Asset', and model Asset that belongs_to :assetable, polymorphic: true
I need to get all the assets of the particular Issue. My firt implementation is as following:
comments = Comment.where(issue_id: issue.id).ids
assets = Asset.where(assetable_id: comments)
However, it is obviously far from perfect. I believe, that this sould be rewritten using joins or something like this, but I fail to wrap my head around it and find a solution. What would you recommend?
You can "manually" join the assets table with comments, as you cannot eagerly load the polymorphic association between them:
Asset
.joins('JOIN comments ON comments.id = assets.assetable_id')
.where(comments: { issue_id: issue.id })
However if a join isn't really needed, where plus select do the trick as well:
Asset.where(assetable_id: Comment.where(issue_id: issue.id).select(:id))
Notice there only one query is performed due to the select(:id) usage instead of ids (which extracts at once every id from the resulting rows):
SELECT assets.*
FROM assets
WHERE assets.assetable_id IN (
SELECT comments.id
FROM comments WHERE
comments.issue_id = ?
)