ActiveRelation that requires nested joins using scopes

I am new to rails. Having a blast. The query API though is giving me some trouble. I've been zooming and doing a lot of stuff very quickly, but this is the first time I have spent hours trying to figure it out. It's not like anything I've used before - regular SQL, or Hibernate, or whatever.

The model I have is pretty simple.

• A PrivateMessage has many Recipients
• A Recipient has a Receiver (which of class User)
  • recipient also has fields for 'is_read' and 'is_deleted'

My goal is to build a query that finds all the unread and not deleted private messages for a given user. To accomplish this, we need to join 'private_messages' to 'recipients'... and then 'recipients' to 'users'.

Here's the relevant User model code:

has_many :sent_messages, :class_name => 'PrivateMessage', :foreign_key => 'sender_id'
has_many :recipient_of_messages, :class_name => 'Recipient'开发者_JAVA技巧, :foreign_key => 'receiver_id'

scope :by_id, lambda { |id| where(:id => id) } 

My Recipient model has the following relevant code:

belongs_to :receiver, :class_name => 'User', :foreign_key => "receiver_id"
belongs_to :private_message

scope :unread, where(:is_read => false).where(:is_deleted => false)
scope :by_receiver_id, lambda { |id| Recipient.joins(:receiver).merge(User.by_id(id)) }
scope :unread_by_receiver_id, lambda { |id| unread.by_receiver_id(id) }

When tested in isolation, this works 100%.

However, when I code the private message queries, I run into problems.

belongs_to :sender, :class_name => 'User'
has_many :recipients, :class_name => 'Recipient'

scope :sorted, order("private_messages.created_at desc")
scope :non_deleted, where(:is_deleted_by_sender => false)
scope :non_deleted_by_sender_id, lambda { |id| sorted.non_deleted.joins(:sender).merge(User.by_id(id)) }

# this scope does not work
scope :non_deleted_by_receiver_id, lambda { |id| sorted.joins(:recipients).merge(Recipient.by_receiver_id(id)) }
scope :newest, sorted.limit(3)

# this scope does not work either
scope :newest_unread_by_receiver_id, lambda { |id| newest.joins(:recipients).merge(Recipient.unread_by_receiver_id(id)) }

When I try and use 'newest_unread_by_receiver_id' or 'non_deleted_by_receiver_id', I get the following exception:

ActiveRecord::ConfigurationError: Association named 'receiver' was not found; perhaps you misspelled it?

This doesn't make much sense to me... because if the name was spelled wrong, why doesn't it fail when I test it isolation?

Can someone help me out please? This one is driving me nuts. At times like this, I just want to program in full sql or Hibernate QL so I could just be done with it :(

If I'm just approaching the problem totally wrong, then I'd appreciate it if you just let me know that too. I am under the impression that using scopes and ActiveRelation was the way moving forward in Rails 3.1.

Thanks

I would probably use something like this. I kept scopes separate for clarity.

Models (renamed PrivateMessage -> Message and Recipient -> MessageCopy):

class User < ActiveRecord::Base
  has_many :sent_messages, :class_name => "Message", :foreign_key => :sender_id
  has_many :sent_message_copies, :through => :sent_messages, :source => :message_copies
  has_many :received_messages, :through => :received_message_copies, :source => :message
  has_many :received_message_copies, :class_name => "MessageCopy", :foreign_key => :recipient_id
end

class Message < ActiveRecord::Base
  belongs_to :sender, :class_name => "User"
  has_many :message_copies
  has_many :recipients, :through => :message_copies
end

class MessageCopy < ActiveRecord::Base
  belongs_to :message
  belongs_to :recipient, :class_name => "User"
  scope :unread, where(:read => false)
  scope :undeleted, where(:deleted => false)
  scope :sent_to, lambda { |recipient| where(:recipient_id => recipient.id) }
end

Schema (migrations would have taken too much space here):

ActiveRecord::Schema.define(:version => 20110503061008) do
  create_table "message_copies", :force => true do |t|
    t.boolean  "read",         :default => false
    t.boolean  "deleted",      :default => false
    t.integer  "message_id"
    t.integer  "recipient_id"
  end
  create_table "messages", :force => true do |t|
    t.string   "title"
    t.integer  "sender_id"
  end
  create_table "users", :force => true do |t|
    t.string   "name"
  end
end

--edit

Example query using joins returning messages

Message.joins(:message_copies).where(:message_copies => {:read => false, :deleted => false, :recipient_id => 3})

Message scope reusing scopes on other model

scope :non_deleted_by_recipient, lambda { |recipient|
  joins(:message_copies).merge(MessageCopy.unread.undeleted.sent_to(recipient))
}

--edit2

This Railscast has nice examples of both joins and scopes:

• http://railscasts.com/episodes/215-advanced-queries-in-rails-3

Eventhough you seem to have found an answer I would like to show you how i have done it in my application:

message table:
id, sender_id, recipient_id, conversation_id, sender_deleted_at, recipient_deleted_at, title, body, (whatever you like)

conversation table:
id, sender_id, recipient_id, conversation_id, sender_deleted_at, etc.

class User < ActiveRecord::Base
  has_many :messages
  has_many :conversations
  has_many :sent_messages, :class_name => "Message", :foreign_key => "sender_id", 
                           :conditions => "sender_deleted_at IS NULL", :dependent => :destroy, :order => "created_at DESC"
  has_many :recieved_messages, :class_name => "Message", :foreign_key => "recipient_id", 
                            :conditions => "recipient_deleted_at IS NULL", :dependent => :destroy, :order => "created_at DESC"
  has_many :created_conversations, :class_name => "Conversation", :foreign_key => "sender_id"
  has_many :recieved_conversations, :class_name => "Conversation", :foreign_key => "recipient_id"
end

class Message < ActiveRecord::Base
  belongs_to :sender, :class_name => "User", :foreign_key => "sender_id"
  belongs_to :recipient, :class_name => "User", :foreign_key => "recipient_id"
  belongs_to :conversation

  before_create :assign_conversation
  after_create  :save_recipient, :set_replied_to, :send_receipt_reminder
end

class Conversation < ActiveRecord::Base
  belongs_to :sender, :class_name => "User", :foreign_key => "sender_id"
  belongs_to :recipient, :class_name => "User", :foreign_key => "recipient_id"

  has_many :messages

  scope :conversations_for_user, lambda {|user| {:conditions => ["sender_id = :user OR   recipient_id = :user", :user => user] }}
end

This way you can fetch pretty much anything and it also enables you to display messages as conversations. You can fetch unread messages in the current conversation, you can fetch all messages for a given conversation or user, etc. etc.

Also you only have one record for every message which seems like a nice solution. I can also give you the additional methods in case you dont want to write them yourself.

Regards Stefano

PS: dont just copy-pasta my code I might have some spelling mistakes in it. Didnt have time to proof, sorry.

Related: https://gist.github.com/rails/rails/pull/5494