Activerecord - Querying Polymorphic Associations

ActiveRecord - querying polymorphic associations

Argh!

I think I found the problem.

When joining via:

@comments = Comment.find(:all,
        :joins => "forum_topics",
        :conditions => ["forum_topics.featured = ? ", true] 
        )

You need the whole join!

:joins => "INNER JOIN forum_topics ON forum_topics.id = comments.commentable_id",

See the ever-awesome:
http://guides.rubyonrails.org/active_record_querying.html#joining-tables

How to query polymorphic associations when I have to join another table on the polymorphic association?

Since there is no table called consumer, joins will fail on this. To solve this problem you can write a custom join.

Try this:

member = Member.find_by(email: 'test1@gmail.com')
Log.joins("LEFT JOIN messages ON (messages.id = logs.consumer_id AND consumer_type='Message')")
   .joins("LEFT JOIN assessments ON (assessments.id = logs.consumer_id AND consumer_type='Assessment')")
   .where('messages.member_id = ? or assessments.member_id = ?', member.id, member.id)

You can nested joins as well to join with members table, notice the AS in the second nested join:

Log.joins("LEFT JOIN messages ON (messages.id = logs.consumer_id AND consumer_type='Message')")
   .joins("LEFT JOIN assessments ON (assessments.id = logs.consumer_id AND consumer_type='Assessment')")
   .joins('LEFT JOIN members ON members.id = messages.member_id')
   .joins('LEFT JOIN members AS mmbrs ON mmbrs.id = assessments.member_id')
   .where('members.email = ? OR mmbrs.email = ?', 
          'john@cheese.com', 'john@cheese.com')

Scopes and Queries with Polymorphic Associations

After reviewing previous answers and playing around with it: here is what I got to work.

(Note that Pet.remove_dogs_that_cannot_sit returns an array. This class method is readable but has the drawback of being slow due to N + 1. Any suggestions for fixing that would be greatly appreciated.)

class Dog < ActiveRecord::Base
  has_many :pets, as: :animal
  scope :sits, -> {where(sits: true)}
end

class Cat < ActiveRecord::Base
  has_many :pets, as: :animal
  scope :killer, ->{ where("killed_mice >= ?", 10) }
end

class Pet < ActiveRecord::Base
  belongs_to :animal, polymorphic: true

  scope :by_type, ->(type) {where(animal_type: type)}
  scope :by_dogs, -> {by_type("Dog") }
  scope :by_cats, -> {by_type("Cat") }

  def self.sitting_dogs
    all.by_dogs
       .joins("INNER JOIN dogs on animal_type = 'Dog' and animal_id = dogs.id")
       .merge(Dog.sits)
  end

  def self.killer_cats
    all.by_cats
       .joins("INNER JOIN cats on animal_type = 'Cat' and animal_id = cats.id")
       .merge(Cat.killer)
  end

  # returns an Array not Pet::ActiveRecord_Relation
  # slow due to N + 1
  def self.remove_dogs_that_cannot_sit
    all.reject{|pet| pet.animal_type == "Dog"  && !pet.animal.sits}
  end
end

---

Related Topics