How to define following using relationships

Question

I need to have one table in which i can set user preferences I want to have one table in which all preferences values are set and another relation table in which i will have user_id and the preference_id but I need to have preference for three different pages

1. item
2. favorite
3. recent.

I need to show the list of results as set by user for e.g if on item page user wants 20 results to be shown then I want to set prference id set as 1 which as value 20.

prefrence table 
id
value

user_preferences 
id
item_result (foreign_key prefernce_id)
favorite_result (foreign_key preference_id)
recent_result (foreign_key preference_id)

I want something which will allow me to extract values like we do user.item.name same way I want to do user_preference.item_result.value

Answer 1

You are defining your relations in a way I think is not best. You are trying to create a many-to-many relation between users and preferences (a user has many preferences, and a preference is set by many users). If you want to add extra fields (value in this case) to a many-to-many relation, you add it to the join table (user preferences in this case).

I noticed that you use static values for the preferences and do not wish to create a model for the preferences themselves (although this would be handy if you decide to expand user preferences in the future).
Try these models:

class User < AR::Base
  has_many :user_preferences

  def item_preference
    user_preferences.find_by_preference_id(1).value
  end
end

class UserPreference < AR::Base
  # has 3 fields: user_id, preference_id and value
  belongs_to :user
end

This way, you can find a preference by doing user.item_preference or user.recent_preference. If you start having many preferences this won't be the optimal way to go about it, but that is something you'll face when you have to.

Optimally, you should have a 3rd model called "Preference" and the UserPreference model would act as a join between Preference and User models.

Answer 2

I resolved the issue on my own.. Before posting answer I must say everyone should user rails console

I have define relations as follows

class User < AR::Base
  has_one :item_preference
  has_one :item_preference, :through => :user_preference, :source => :item

  has_one :favorite_preference
  has_one :favorite_preference, :through => :user_preference, :source => :favorite

  has_one :recent_preference
  has_one :recent_preference, :through => :user_preference, :source => :recent

  has_one :user_preference
end

class UserPreference < ActiveRecord::Base
  belongs_to :item,
  :foreign_key => "item_results",
  :class_name => "Preference"

  belongs_to :favorite,
  :foreign_key => "favorite_results",
  :class_name => "Preference"

  belongs_to :recent,
  :foreign_key => "recent_results",
  :class_name => "Preference"

  belongs_to :user
end

class Preference < ActiveRecord::Base
  has_many :item_preferences,
    :foreign_key => "item_results",
    :class_name => "UserPreference"

  has_many :favorite_preferences,
    :foreign_key => "favorite_results",
    :class_name => "UserPreference"

  has_many :recent_preferences,
    :foreign_key => "recent_results",
    :class_name => "UserPreference"

end  

now I can access the values as

User = User.find(16)
user.item_preference
[:] [DEBUG] [2010-09-16 17:09:54] [           App::OnlineOrdering] -   Preference Load (0.4ms)   SELECT `preferences`.* FROM `preferences` INNER JOIN `user_preferences` ON `preferences`.id = `user_preferences`.item_results WHERE ((`user_preferences`.user_id = 16)) 
=> #<Preference id: 1, value: "20", created_at: "2010-09-15 15:13:04", updated_at: "2010-09-15 15:13:04">

and other way around

preference = Preference.find(1)

preference.item_preferences
[:] [DEBUG] [2010-09-16 17:09:23] [           App::OnlineOrdering] -   UserPreference Load (0.4ms)   SELECT * FROM `user_preferences` WHERE (`user_preferences`.item_results = 1) 
[:] [DEBUG] [2010-09-16 17:09:23] [           App::OnlineOrdering] -   UserPreference Columns (1.2ms)   SHOW FIELDS FROM `user_preferences`
=> [#<UserPreference id: 1, user_id: 16, item_results: 1, favorite_results: 2, recent_results: 2, created_at: "2010-09-15 15:13:04", updated_at: "2010-09-15 15:13:04">]