Porting complicated has_many relationships to Rails >4.1 (without finder_sql)

Question

I am porting a Rails app to Rails 4.2. This Rails app contains some rather complex manual SQL code in associations - partly due to DB optimizations (e.g. subselects instead of JOINs), partly due to no feasible alternative at the time of writing (Rails 3.0), partly surely due to lack of knowledge (I hope, at least - that would be easy to solve).

Example: An InternalMessage class. Messages can be sent between users (Recipients of an InternalMessage, and 'deletions' of messages, are stored in InternalMessagesRecipients, since there can be several) and they can be read, replied to, forwarded and deleted. The association looks like this:

class User < AR::Base
  has_many :internal_messages,
      :finder_sql => "SELECT DISTINCT(internal_messages.id), internal_messages.* FROM internal_messages " +
          ' LEFT JOIN internal_messages_recipients ON internal_messages.id=internal_messages_recipients.internal_message_id' +
          ' WHERE internal_messages.sender_id = #{id} OR internal_messages_recipients.recipient_id = #{id}',
      :counter_sql => 'SELECT count(DISTINCT(internal_messages.id)) FROM internal_messages ' +
          ' LEFT JOIN internal_messages_recipients ON internal_messages.id=internal_messages_recipients.internal_message_id' +
          ' WHERE internal_messages.sender_id = #{id} OR internal_messages_recipients.recipient_id = #{id}'
  # ...
end

The key part is the "OR" clause at the end - with this association I want to get both received and sent messages, which are joined with the user table seperately:

  has_many :sent_messages, -> { where(:sender_deleted_at => nil) }, :class_name => 'InternalMessage', :foreign_key => 'sender_id' #, :include => :sender
  has_many :internal_messages_recipients, :foreign_key => 'recipient_id'
  has_many :rcvd_messages, :through => :internal_messages_recipients,  :source => :internal_message, :class_name => 'InternalMessage'

since an InternalMessage might have several recipients (and can also be sent to the sender himself).

Q: How do I port this finder_sql to a Rails 4.2 compatible has_many definition?

Fillagree

Answer 1

Update

I learnt a while ago that this makes no sense. A has_many relationship must have injective connections at least in one direction, so an "OR" in a SQL clause makes no sense. How should a CREATE operation decide which condition to satisfy to create a new record? This relationship is read only by definition and so it is not a has_many relationship.

In this case, a simple class method (or scope) would be the right answer instead of has_many. To concatenate results from several queries use something like

def internal_messages
  InternalMessage.where( id: sent_message_ids + received_message_ids)
end

to keep the resulting object chainable (i.e. @user.internal_messages.by_date etc.)

Fillagree

Of course, this is inferior if you want memoization of results, particularly so they work with reload. Is there any way to get rails to maintain these relationships without the foreign key? – Overlooker

Answer 2

Pass the proc that contains the SQL string as scope.

has_many :internal_messages, -> { proc { "SELECT DISTINCT(internal_messages.id), internal_messages.* FROM internal_messages " +
      ' LEFT JOIN internal_messages_recipients ON internal_messages.id=internal_messages_recipients.internal_message_id' +
      ' WHERE internal_messages.sender_id = #{id} OR internal_messages_recipients.recipient_id = #{id}' } }

Woodsum

OK, thanks. How about counter_sql? Not required any more? With Rails 3 I used to get SQL errors when not using counter_sql because of DISTINCT. – Fillagree

I've never really done this by myself but this stackoverflow answer says that you can define your own count method inside a block given to has_many. So I guess you can do something like def count proxy_association.owner.class.count_by_sql("YOUR SQL HERE"); end in the block. – Woodsum

update: Rails 4.2.5.1, ruby 2.3.1p112 has_many :talks, ->{ proc { "SOME SQL"}} NoMethodError: undefined method 'except' for #<Proc... vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/associations/association_scope.rb:158:in `block (2 levels) in add_constraints' – Minster