How do I pluck a distinct column value from an associated model in a polymorphic association?

Question

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'.

Ailey

If you post an example, It will make more easy for others to help you. – Maurya

Your associations don't make sense to me. How does the ref_id column of Comment relate to the primary keys of both Article and Book? – News

I have :foreign_key => 'ref_id' while defining association. – Ailey

Answer 1

The easy way:

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.

The reusable way:

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.

Reciprocation

If book and article has the same ID they will be loaded both, and one of them will be wrong. Must be included the following code: ``` belongs_to :book, -> { where('ref_type = ?', 'Book') }, class_name: 'Book', foreign_key: 'ref_id' belongs_to :article, -> { where('ref_type = ?', 'Article') }, class_name: 'Article', foreign_key: 'ref_id' ``` – Maxfield

Answer 2

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

Hygrometer

Answer 3

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

Knowall

Answer 4

I recommend you look at this tutorial: https://web.archive.org/web/20180928102044/http://karimbutt.github.io:80/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.

Juryman