CODEWITHSUNDEEP
sqlruby-on-railsrubyruby-on-rails-3activerecord
user502052
user502052

Reputation: 15257

How to retrieve associated objects / records that a set of users have in common through a association table?

I am using Ruby on Rails 3.2.2 and I would like to retrieve objects / records simultaneously associated by two or more users. That is, I have a database table where I store association data between users and articles; I would like to "build" a SQL query so to retrieve associated articles by two or more users. For instance, if I have followings association objects

#<UserArticleAssociation id: 1, user_id: 1, article_id: 1>
#<UserArticleAssociation id: 2, user_id: 1, article_id: 2>
#<UserArticleAssociation id: 3, user_id: 1, article_id: 3>
#<UserArticleAssociation id: 4, user_id: 2, article_id: 1>
#<UserArticleAssociation id: 5, user_id: 2, article_id: 2>    
#<UserArticleAssociation id: 6, user_id: 3, article_id: 1>
#<UserArticleAssociation id: 7, user_id: 3, article_id: 3>
#<UserArticleAssociation id: 8, user_id: 4, article_id: 4>

I would to state / run a scope method so to get something like the following:

@user1.articles.associated_by(@user2)
# => [ #<UserArticleAssociation id: 1, user_id: 1, article_id: 1>,
       #<UserArticleAssociation id: 4, user_id: 2, article_id: 1>]

@user1.articles.associated_by(@user3)
# => [ #<UserArticleAssociation id: 1, user_id: 1, article_id: 1>,
       #<UserArticleAssociation id: 7, user_id: 3, article_id: 3>]

@user1.articles.associated_by(@user4)
# => nil

@user2.articles.associated_by(@user3)
# => [ #<UserArticleAssociation id: 1, user_id: 1, article_id: 1>]

@user1.articles.associated_by([@user2, @user3])
# => [ #<UserArticleAssociation id: 1, user_id: 1, article_id: 1>]

In others words, I would like to find articles that a set of users have in common through the user_article_associations table. How can I make that?

Involved classes are stated as

class User < ActiveRecord::Base
  has_many :article_associations, :class_name  => 'UserArticleAssociation'
  has_many :articles, :through => :article_associations
end

class Article < ActiveRecord::Base
  has_many :user_associations
  has_many :users, :through => :user_associations
end

Upvotes: 1

Views: 204

Answers (3)

Yuri Barbashov
Yuri Barbashov

Reputation: 5437

You should use having clause with group_by

Article.joins(:user_article_associations).
where('user_article_associations.user_id in (?)', users_ids)).
group('articles.id').
having('COUNT(user_article_associations.user_id in (?)) = ?',users_ids, users_ids.size)

Upvotes: 1

Salil
Salil

Reputation: 9722

Maybe something like this will work: 

Article.joins(:users).where('users.id in (?)', my_users.map(&:id)).group('articles.id')

Upvotes: 0

Andrew Marshall
Andrew Marshall

Reputation: 96944

Use merge to combine the relations:

articles = @user1.articles.merge(@user2.articles)

This will get you all Articles that @user1 & @user2 share. You can further call merge with additional relations for Articles, e.g.:

articles = @user1.articles.merge(@user2.articles).merge(@user3.articles)

Upvotes: 1

Related Questions