How to select all recipients from the message database?

Question

MySQL

There is a table of personal messages of the service's internal mail. For the problem, the following fields are significant:

• Message ID
  
• sender ID
  
• Recipient ID
  
• Message text
  
• departure date
  

I can’t figure out how it is possible to more or less beautifully and logically select from this table a list of addressees with whom this user has communicated? And it would be nice to select the last message in the dialog to display its date.
I use Yii, maybe it has some ready-made solutions for this bike?

Answer 1

Here is essentially the solution: goo.gl/s724k , but there with a subquery, although I think there is no other way.

Answer 2

If I were you, I would store the id of the last message in each dialog somewhere else. Then it would be possible to select all records from the database where the current user is either the recipient or the sender, and then just group them by the id of the last message. Then you would get all the latest messages from that user.

Answer 3

$criteria = new CDbCriteria();
$criteria->distinct = true;
$criteria->addCondition('sender_id = :sender');
$criteria->select('recipient_id');
$criteria->params = array(':sender' => Yii::app()->getUser()->getId());

$userMessages = Messages::model()->findAll($criteria);
$uniqRecipients = array();
foreach($userMessages as $model)
   $uniqRecipients[] = $model->recipient_id;

something like this. The code is not working, I wrote immediately in the comment.

Answer 4

I recently solved this problem myself. Truth on Zend. Here's how I did it:

$mess_db = new Application_Model_DbTable_Message();
$user_id = ...;

$mess_list = $mess_db->getMessageList($user_id);

foreach ($mess_list as $mess){
  if(!isset($dialog_list[$dialog_companion])){
    $dialog_list[$dialog_companion] = $mess;
  }
}

$this->view->dialogs = $dialog_list;

Immediately I get an array of "dialogs" and the last message.
$mess_db->getMessageList - model, returns an array of all user messages.

Answer 5

SELECT 
  `pm`.*,
  `recipient`.* 
FROM
  `private_message` `pm` 
  LEFT OUTER JOIN `user` `recipient` 
    ON (
      `pm`.`recipient_id` = `recipient`.`id`
    ) 
WHERE `sender_id` = 5 
GROUP BY `recipient_id` 
ORDER BY `pm`.`create` ASC;

Here is the SQL solution for my schema. Fitting under Yii should not be a problem.
In the user model:

public function relations()
{
return array(
'lastMessage' => array(self::HAS_ONE, 'PrivateMessage', 'recipient_id', 'order' => 'lastMessage.time DESC'),
'messages' => array(self::HAS_MANY, 'PrivateMessage', 'recipient_id', 'order' => 'lastMessage.time DESC'),
}

Choose as:

$criteria->with = array('messages' => array('with' => 'recipient'), 'lastMessage');
$criteria->group = 'recipient.id';

Something like that