Rails: Querying through 2 join table associations

Question

I have a quite complicated relation between models and are now frustrated by a SQL Query to retrieve some objects.

given a Product model connected to a category model via a has_many :through association and a joint table categorization. Also a User model connected to this category model via a has_many :through association and a joint table *category_friendship*.

I am now facing the problem to retrieve all products, which are within the categories of the array user.category_ids. However, I can't just not manage to write the WHERE statement properly.

I tried this:

u = User.first
uc = u.category_ids
Product.where("category_id IN (?)", uc)

However this won't work, as it doesn't have a category_id in the product table directly. But how can I change this to use the joint table categorizations?

I'm giving you the model details, maybe you find it helpful for answering my question:

Product.rb

class Product < ActiveRecord::Base

 belongs_to :category

 def self.from_users_or_categories_followed_by(user)
 cf = user.category_ids
 uf = user.friend_ids

 where("user_id IN (?)", uf) # Products out of friend_ids (uf) works fine, but how to extend to categories (cf) with an OR clause?
 end

Category.rb

class Category < ActiveRecord::Base
 has_many :categorizations
 has_many :products, through: :categorizations
 has_many :category_friendships
 has_many :users, through: :category_friendships

Categorization.rb

class Categorization < ActiveRecord::Base

 belongs_to :category
 belongs_to :product

Category_friendship.rb

class CategoryFriendship < ActiveRecord::Base

 belongs_to :user
 belongs_to :category

User.rb

class User < ActiveRecord::Base

has_many :category_friendships
has_many :categories, through: :category_friendships

def feed
 Product.from_users_or_categories_followed_by(self) #this should aggregate the Products
end

If you need more details to answer, please feel free to ask!

AnkitG AnkitG · Accepted Answer · 2012-09-04T12:02:11

Looking at the associations you have defined and simplifying things. Doing a bit refactoring in what we have to achieve.

Product.rb

class Product < ActiveRecord::Base

  belongs_to :category

 end

User.rb

  class User < ActiveRecord::Base
        has_many :categories, through: :category_friendships
        scope :all_data , includes(:categories => [:products])

   def get_categories
     categories
   end

   def feed
      all_products = Array.new
      get_categories.collect {|category| category.get_products }.uniq
   end
  end

Category.rb

class Category < ActiveRecord::Base
 has_many :users, through: :category_friendships
 has_many :products

 def get_products
   products
 end
end

NO NEED OF CREATING CATEGORY_FRIENDSHIP MODEL ONLY A JOIN TABLE IS NEEDED WITH NAME CATEGORIES_FRIENSHIPS WHICH WILL JUST HAVE USER_ID AND CATEGORY_ID

USAGE: UPDATED

Controller

  class UserController < ApplicationController
         def index
           @all_user_data = User.all_data
        end
   end

view index.html.erb

<% for user in @all_user_data %>
 <% for products in user.feed %>
  <% for product in products %>
       <%= product.name %>
     end
  end
end

Rails: Querying through 2 join table associations

2 Answers