has_one association on polymorphic model

I have the following models (stripped down for simplicity) :

class Activity < ActiveRecord::Base
  belongs_to :trackable, polymorphic: true
  belongs_to :user
  has_one :comment, as: :trackable
end

class Comment < ActiveRecord::Base
  belongs_to :commentable, polymorphic: true
  belongs_to :user
end

class Note < ActiveRecord::Base
  belongs_to :user
  has_many :comments
end

There are Notes with the ids 1, 2, and 3. There are Comments on these notes.
I would like to get the ids for the Activities where a Comment was created on any of these Notes.

I run the following query:

Activity.where({
    trackable_type: "Comment",
    action: "comment.create",
    comments: { commentable_type: "Note", commentable_id: [1,2, 3] }
  })
  .joins(:comment)
  .ids

But the result is empty.

QUESTION: What association do I have to add to the Activity model to make it join the comment model in this query?

Multiple polymorphic associations can be tough because you can't do direct joins in SQL with polymorphic relationships since you're setting the table name in the column itself.

You'll have to make two separate queries along the lines of this:

@comment_ids = Comment.where(commentable_type: "Note", commentable_id: [1,2,3]).pluck(:id)
@activities = Activity.where(trackable_type: "Comment", trackable_id: @comment_ids)

That will first query for the comment IDs on those notes, then it will load up all the activities for those comments on the notes.

Awesome response time Chris!

This solution feels simple enough. I believe I can remove the following line from the Activity class:

has_one :comment, as: :trackable

My only concern is that there could be a lot of queries depending on how many times I have to do something like the following (untested code):

@comment_ids = Comment.where(commentable_type: "Note", commentable_id: [1,2,3]).pluck(:id)
@note_ids = Note.where(user_id: [4, 5, 6]).ids
@note_ids += Note.where("created_at > ? ", 10.days.ago).ids
# additional model ids...

@activity_ids = Activity.where(trackable_type: "Comment", trackable_id: @comment_ids).ids
@activity_ids += Activity.where(trackable_type: "Note", trackable_id: @note_ids).ids
# additional Activity ids...

@activities = Activity.find(@activity_ids).order(created_at: :desc).limit(20)

where I'm trying to build a list of Activities based on specific criteria. Is there a way to optimize this?

You got it! :) So question for you: what are you trying to accomplish exactly with these queries? It might make sense to structure your data a little bit differently to make this more efficient. Your models sound like they're designed to separate everything, but your queries sound like you want don't actually want things separated.

For example, you're wanting to treat activities on notes and activities on comments the same, but your database is structured such that they're separate. It might make more sense to only have activities on the Note level and then include metadata for the related objects like Comment instead.

I'm trying to build an activity feed based on user selected conditions.

Example:

• user follows Comments 1, 2, and 3
• user follows Note 4, 5, and 6
• user would like to be notified about Comments on Notes they follow
• show the activities ONLY for these models

There will be an Activity feed for all Activities at example.com/activities.

The user can Follow different records and when the user goes to example.com/followings they will see only activities for records they follow.

class Following < ActiveRecord::Base
  belongs_to :followable, polymorphic: true
  belongs_to :user 
end

I'm sorry if I'm being too vague. I'm trying to figure out the best way to go about this. I would like to use some kind of OR query without adding an additional gem. Example:

# Grab the ids of the models the user is "following"
@note_ids = current_user.followings.where(followable_type: "Note").pluck(:followable_id)
@comment_ids = current_user.followings.where(followable_type: "Comment").pluck(:followable_id)
@comment_ids += Comment.where(commentable_type: "Note", commentable_id: @note_ids).ids

# Get the activities that occurred on those models
@activities = Activity
  .where(trackable_type: "Comment", trackable_id: @comment_ids)
  .or(trackable_type: "Note", trackable_id: @note_ids)
  .limit(20)

I do have the Ransack gem installed. Maybe I could use it somehow? I'll keep searching till I find a viable solution. I really appreciate your help Chris.

EDIT:

So far this is the best solution I've found (tested in IRB with slightly different variable and attribute names):

# Get the Note and Comment ids
@note_ids = current_user.followings.where(followable_type: "Note").pluck(:followable_id)
@comment_ids = current_user.followings.where(followable_type: "Comment").pluck(:followable_id)
@comment_ids += Comment.where(commentable_type: "Note", commentable_id: @note_ids).ids

# Get the activities
input = { "Comment" => @comment_ids, "Note" => @note_ids }
Activity.where("trackable_type = ? AND trackable_id IN (?) OR trackable_type = ? AND trackable_id IN (?)", *input.flatten)

This returns 8 activities (5 Comments, 3 Notes) based on my sample data. This will work untill I find a more optimal solution. Thanks for all your help.