1

I'm have a fairly standard business model with Account instances who can follow each other. It's just like Twitter. The join model is called Relationship.

class Account
  has_many :active_relationships, class_name: 'Relationship', 
    dependent: :destroy, foreign_key: :follower_id
  has_many :passive_relationships, class_name: 'Relationship', 
    dependent: :destroy, foreign_key: :followed_id
  # These are accounts that a given account is following
  has_many :friends, through: :active_relationships, source: :followed
  # These are accounts that follow the given account
  has_many :followers, through: :passive_relationships, source: :follower
end

class Relationship
  belongs_to :follower, class_name: 'Account'
  belongs_to :followed, class_name: 'Account'
end

I'd like to scope to twitter accounts which follow two or more other specific accounts.

For example, imagine four accounts exist in the database. They're named Alan, Becca, Charlie and Dave. Dave follows both Alan and Charlie. Becca only follows Alan. When I apply my scope it should return Dave only.

I can scope to users who follow one specific account like this:

Account.includes(:active_relationships)
  .where(relationships: { followed_id: alan.id })

but chaining where clauses returns zero results because the DB looks for a relationship record where the followed_id is simultaneously equal to two different account ids. This is obviously impossible.

Account.includes(:active_relationships)
  .where(relationships: { followed_id: alan.id })
  .where(relationships: { followed_id: charlie.id })

How do I implement this correctly with ActiveRecord?

David Tuite
  • 22,258
  • 25
  • 106
  • 176
  • Just to make sure I understand -- given two specific accounts (the "followed accounts"), you want ActiveRecord to tell you which account(s) follow them both? – Travis Hohl Oct 22 '14 at 12:53
  • @thohl Yes that's it exactly. – David Tuite Oct 22 '14 at 17:03
  • My gut reaction is to write a scope using a lambda or a named method under Relationship that uses a count > 1 for a given follower. I haven't tried it, but the SQL you'd want it to produce would likely be something on the order of `SELECT DISTINCT id, count(id) as c FROM relationships GROUP BY followed HAVING c > 1`. You might need a custom SQL in Ruby to get at that though. – rdnewman Oct 23 '14 at 02:37

0 Answers0