Ruby on Rails 3: Combine results from multiple has_many or has_many_through associations

Question

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, :finder_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.

Currently I have a chaos of find_by_sql calls scattered around the app. I have yet to update this to use ARel and especially the #arel_table method (see comment below). When I do this I will post my results here. — Jens
I still use finder_sql and counter_sql, but extracted the SQL strings into a class method which I then include via a proc. This is a lot cleaner than before, though I have to rethink again now that we are moving to Rails 4 and finder_sql is deprecated. — Jens

Jonas Schubert Erlandsson Jonas Schubert Erlandsson · Accepted Answer · 2013-01-08T09:41:50

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 ...

Ruby on Rails 3: Combine results from multiple has_many or has_many_through associations

1 Answers