CakePHP PrivateMessage Schema and Associations

I thought I came upon a solution to my problem, but still, my approach doesn't fit all my needs. Therefore I resort to your suggestions. Here is basically what I want to achieve:

I have 3 database tables:

• users: id, username, ...
• private_messages: id, ...
• private_messages_users: id, private_message_id, sender_id, recipient_id, status

Viewing this database schema, you will notice that for each message I create a row in the join table, that means if I send a message to two recipients, there will be two inserted rows in the join table. If you have a better different a开发者_高级运维pproach, I'm open to suggestions.

I need to define associations in order to:

• be able to compose a message; in the compose form, I would like to have a multiple list from which I can select the recipients I would like to send the message to; the sender is not important here, as I will add it manually into the $this->data array before save
• be able to create an inbox and a sent folder, where I would fetch all messages received, respectively, sent
• be able to view a sent message, together with all recipients it was sent to
• be able to mark a message as read when a user views it for the first time
• also, for each sender_id and recipient_id, I would like to fetch the username from the users table

I would like that all actions requested be completed 'cakily', meaning no 'hacks' or methods to transform arrays. Just plain operations, based entirely on the associations created.

Any help would be highly appreciated, as I am struggling with this problem for over a week. Thank you very much!

I'd imagine that you would want a setup like this: Users hasMany UserMessage and then create a UserMessageUsers model which represents your HABTM table. In that table you would have a column called read which indicates if, and which, recipient had read the message.

Here's some SQL that you can run to setup an example of what I'm talking about so you can visualize the idea.

CREATE TABLE `users` (                                        
  `id` CHAR(36) COLLATE utf8_unicode_ci NOT NULL,             
  `name` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,   
  `email` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,  
   PRIMARY KEY (`id`)                                          
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

CREATE TABLE `user_messages` (
  `id` CHAR(36) COLLATE utf8_unicode_ci NOT NULL,               
  `user_id` CHAR(36) COLLATE utf8_unicode_ci NOT NULL,          
  `subject` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,  
  `body` TEXT COLLATE utf8_unicode_ci,         
  PRIMARY KEY (`id`)          
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

CREATE TABLE `user_messages_users` (                            
  `id` CHAR(36) COLLATE utf8_unicode_ci NOT NULL,               
  `user_message_id` CHAR(36) COLLATE utf8_unicode_ci NOT NULL,  
  `user_id` CHAR(36) COLLATE utf8_unicode_ci NOT NULL,          
  `read` TINYINT(1) DEFAULT '0',                                
  PRIMARY KEY (`id`)                                            
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

I use UUIDs for everything, hence the CHAR(36) columns.

So lets say a user, "Bob" sends an email to "Sally" and "Bill" there will be one record put into user_messages which links back to "Bob" as the creator. Then there will be two records in user_messages_users which links the specific user_messages record to both "Sally" and "Bill"

Now if "Sally" reads the message the specific user_messages_users record will change its read column to 1 -- indicating that she has read the message. "Bob"s record will remain 0 -- to indicate that he has not read it.

To perform the changes on the HABTM table you will need to create a model for it. The CakePHP book talks about this, specifically the with option for relationships.