Is there an activerecord relationship to solve this problem?

I can't seem to wrap my head around this. I have three tables:

mysql> desc users;
+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | int(11)      | NO   | PRI | NULL    | auto_increment |
| username             | varchar(255) | YES  |     | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+

mysql> desc mentions;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| message_id       | int(11)      | YES  |     | NULL    |                |
| mentionable_type | varchar(255) | YES  |     | NULL    |                |
| mentionable_id   | int(11)      | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

mysql> desc messages;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| body       | text     | YES  |     | NULL    |                |
| user_id    | int(11)  | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

And the following relationships:

class User < ActiveRecord::Base
  has_many :messages
end

class Message < ActiveRecord::Base
  belongs_to :user
  has_many :mentions
end

class Mention < ActiveRecord::Base
  belongs_to :mentionable, :polymorphic => true
  belongs_to :message
end

I'm not sure if I'm using it correctly, but I used the polymorphic relationship in Mention because mentionable_type could be 'User' or 'Group'. I've left the Group stuff out of this post as it's not related to this question.

When a user creates a Message, their user_id is stored in the messages table. I can easily return a list of a user's "authored" messages with:

current_user.messages

Similar to a tweet, the message's body may, or may not, contain mentions of n users or groups. When the message "I'm having lunch with @userA, @userB, and @groupX." is created, the body would be parsed and those three "mentions" would be created as well.

I can easily return all of a user's "mentions" with:

current_user.mentions

If I want to see the message of a mention, I can do:

开发者_StackOverflow社区

mention = current_user.mentions.first
mention.message

What I can't seeem to figure out is a clean way to combine the two and get a list of messages that a user created AND were mentioned in. Any ideas?

---

I your User model, this line should be present for polymorphic relationships.

class User
  has_many :messages
  has_many :mentions, :as => :mentionable
end

And try this:

user_id = 10
@messages = Message.find(:all, :joins => [:mentions], 
                         :conditions => ['messages.user_id = ?', user_id])