Ruby on Rails 3: Combine results from multiple has_many or has_many_through associations
I have the following models. Users have UserActions, and one possible UserAction can be a ContactAction (UserAction is a polymorphism). There are other actions like LoginAction etc. So
class User < AR::Base has_many :contact_requests, :class_name => "ContactAction" has_many :user_actions has_many_polymorphs :user_actionables, :from => [:contact_actions, ...], :through => :user_actions end class UserAction < AR::Base belongs_to :user belongs_to :user_actionable, :polymorphic => true end class ContactAction < AR::Base belongs_to :user named_scope :pending, ... named_scope :active, ... end
The idea is that a ContactAction joins two users (with other consequences within the app) and always has a receiving and a sending end. At the same time, a ContactAction can have different states, e.g. expired, pending, etc.
I can say @user.contact_actions.pending
or @user.contact_requests.expired
to list all pending / expired requests a user has sent or received. This works fine.
What I would now like is a way to join both types of ContactAction. I.e. @user.contact_actions_or_requests
. I tried the following:
class User def contact_actions_or_requests self.contact_actions + self.contact_requests end # or has_many :contact_actions_or_requests, :finde开发者_高级运维r_sql => ..., :counter_sql => ... end
but all of these have the problem that it is not possible to use additional finders or named_scopes on top of the association, e.g. @user.contact_actions_or_requests.find(...)
or @user.contact_actions_or_requests.expired
.
Basically, I need a way to express a 1:n association which has two different paths. One is User -> ContactAction.user_id
, the other is User -> UserAction.user_id -> UserAction.user_actionable_id -> ContactAction.id
. And then join the results (ContactActions) in one single list for further processing with named_scopes and/or finders.
Since I need this association in literally dozens of places, it would be a major hassle to write (and maintain!) custom SQL for every case.
I would prefer to solve this in Rails, but I am also open to other suggestions (e.g. a PostgreSQL 8.3 procedure or something simliar). The important thing is that in the end, I can use Rails's convenience functions like with any other association, and more importantly, also nest them.
Any ideas would be very much appreciated.
Thank you!
To provide a sort-of answer to my own question:
I will probably solve this using a database view and add appropriate associations as needed. For the above, I can
- use the SQL in finder_sql to create the view,
- name it "contact_actions_or_requests",
- modify the SELECT clause to add a user_id column,
- add a app/models/ContactActionsOrRequests.rb,
- and then add "has_many :contact_actions_or_requests" to user.rb.
I don't know how I'll handle updating records yet - this seems not to be possible with a view - but maybe this is a first start.
The method you are looking for is merge. If you have two ActiveRecord::Relations, r1 and r2, you can call r1.merge(r2) to get a new ActiveRecord::Relation object that combines the two.
If this will work for you depends largely on how your scopes are set up and if you can change them to produce a meaningful result. Let's look at a few examples:
Suppose you have a Page model. It has the normal created_at and updated_at attributes, so we could have scopes like: :updated -> { where('created_at != updated_at') } :not_updated -> { where('created_at = updated_at') }
If you pull this out of the database you'll get:
r1 = Page.updated # SELECT `pages`.* FROM `pages` WHERE (created_at != updated_at)
r2 = Page.not_updated # SELECT `pages`.* FROM `pages` WHERE (created_at = updated_at)
r1.merge(r2) # SELECT `pages`.* FROM `pages` WHERE (created_at != updated_at) AND (created_at = updated_at)
=> []
So it did combine the two relations, but not in a meaningful way. Another one:
r1 = Page.where( :name => "Test1" ) # SELECT `pages`.* FROM `pages` WHERE `pages`.`name` = 'Test1'
r2 = Page.where( :name => "Test2" ) # SELECT `pages`.* FROM `pages` WHERE `pages`.`name` = 'Test2'
r1.merge(r2) # SELECT `pages`.* FROM `pages` WHERE `pages`.`name` = 'Test2'
So, it might work for you, but maybe not, depending on your situation.
Another, and recommended, way of doing this is to create a new scope on you model:
class ContactAction < AR::Base
belongs_to :user
scope :pending, ...
scope :active, ...
scope :actions_and_requests, pending.active # Combine existing logic
scope :actions_and_requests, -> { ... } # Or, write a new scope with custom logic
end
That combines the different traits you want to collect in one query ...
精彩评论