If I have a polymorphic association between 3 models as:
Comment
belongs_to :book, :class_name => 'Book', :foreign_key => 'ref_id', conditions: "comments.ref_type = 'Book'"
belongs_to :article, :class_name => 'Article', :foreign_key => 'ref_id', conditions: "comments.ref_type = 'Article'"
belongs_to :ref, :polymorphic => true
How can I pick distinct values from Title
column of both Book
and Article
models for a given list of comments?
For example, if I have to list the titles for books and article for which comments have been given in a time period then how can I do that? I can easily pick the comment list but how do I pick related unique titles from Book
and Article
?
For example:
Book
+--------------+
| Id | Title |
+----+---------+
| 1 | 'Book1' |
| 2 | 'Book2' |
| 3 | 'Book3' |
+--------------+
Article
+-----------------+
| Id | Title |
+----+------------+
| 1 | 'Article1' |
| 2 | 'Article2' |
+-----------------+
Comments
+--------------------------------------+
| Id | comment | ref_id | ref_type |
+----+------------+--------+-----------+
| 1 | 'comment1' | 1 | Book |
| 2 | 'comment2' | 1 | Book |
| 3 | 'comment3' | 1 | Article |
| 4 | 'comment4' | 3 | Book |
+--------------------------------------+
I need the list of title to be 'Book1'
, 'Book3'
, 'Article1'
.
I think the simplest way, without directly using the Arel
API or iterating over arrays, would be to define .titles_for_comments
scopes on both Book
and Article
that let you select distinct titles from each table when given a collection of comments
. Then define a .distinct_titles
scope for Comment
that uses both [Book|Article].titles_for_comments
. For example, the model and scope definitions below let you find the distinct Book and Article titles for any given Comment::ActiveRecord_Relation
instance by calling Comment.distinct_titles
class Book < ActiveRecord::Base
has_many :comments, as: :ref
def self.titles_for_comments(comment_ids)
joins(:comments).where(comments: { id: comment_ids }).distinct.pluck(:title)
end
end
class Article < ActiveRecord::Base
has_many :comments, as: :ref
def self.titles_for_comments(comment_ids)
joins(:comments).where(comments: { id: comment_ids }).distinct.pluck(:title)
end
end
class Comment < ActiveRecord::Base
belongs_to :ref, polymorphic: true
def self.distinct_titles
comment_ids = ids
Article.titles_for_comments(comment_ids) + Book.titles_for_comments(comment_ids)
end
end
You can download this Gist and run it with ruby polymorphic_query_test.rb
and the tests should pass https://gist.github.com/msimonborg/907eb513fdde9ab48ee881d43ddb8378
Comment.all.includes(:ref).map { |comment| comment.ref.title }.uniq
Fetch all comments, eager load their refs, and return an array containing unique titles. The eager loading part is not strictly necessary, but it might perform better. 3 queries are executed, one for the comments and one for each kind of ref. You can replace all with any scope. Note that this fetches all comments and their refs and uses ruby to turn it into an array, rather than SQL. This works perfectly, but performance might suffer. It's usually better to use distinct to get unique values and pluck to get an array of these values.
This approach works with all kinds of ref. So if we introduce a third kind of ref, e.g. Post, it will automatically be included in this query.
class Comment < ApplicationRecord
belongs_to :book, class_name: 'Book', foreign_key: 'ref_id'
belongs_to :article, class_name: 'Article', foreign_key: 'ref_id'
belongs_to :ref, polymorphic: true
scope :with_ref_titles, lambda {
book_titles = select('comments.*, books.title').joins(:book)
article_titles = select('comments.*, articles.title').joins(:article)
union = book_titles.union(article_titles)
comment_table = arel_table
from(comment_table.create_table_alias(union, :comments).to_sql)
}
end
This scope uses arel and a UNION of subqueries to fetch the titles. It basically adds the ref's title to the comment objects. Since scopes should be chainable, it returns an ActiveRecord Relation rather than an array. To get distinct titles, append distinct.pluck(:title).
comments = Comment.with_ref_titles
comments.distinct.pluck(:title) # => ["Article1", "Book1", "Book3"]
comments.where('title LIKE "Book%"').distinct.pluck(:title) # => ["Book1", "Book3"]
The SQL-query produced by this scope looks like this:
SELECT DISTINCT "title" FROM ( SELECT comments.*, books.title FROM "comments" INNER JOIN "books" ON "books"."id" = "comments"."ref_id" UNION SELECT comments.*, articles.title FROM "comments" INNER JOIN "articles" ON "articles"."id" = "comments"."ref_id" ) "comments"
Tested with rails 5.1.2 and sqlite.
Try this one
titles_from_books = Comment.joins('INNER JOIN books on comments.ref_id = books.id').where('comments.ref_type = ?','Book').pluck('books.title')
titles_from_articles = Comment.joins('INNER JOIN articles on comments.ref_id = article.id').where('comments.ref_type = ?','Article').pluck('articles.title')
final_titles = (titles_from_books + titles_from_articles).uniq
I recommend you look at this tutorial: http://karimbutt.github.io/blog/2015/01/03/step-by-step-guide-to-polymorphic-associations-in-rails/ and see if it's feasible for you.
If you control the model code, I would set up comments to belong_to a generic commentable object. Example:
class Comment < ActiveRecord::Base
belong_to :commentable, :polymorphic => true
end
And
class Book < ActiveRecord::Base
has_many :comments, as: commentable
end
class Article < ActiveRecord::Base
has_many :comments, as: commentable
end
Then given any group of comments you could run
Comments.each do |comment|
comment.commentable.title
end.uniq
This may seem like a lot of work to just get titles at the moment, but if you stick with this project, I expect books and articles to share a lot of code of this type, as well as maybe adding other commentable objects in the future. Overall having a generic object will save a lot of work.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With