Q:

How to show user messages inbox based on conversation query?

I'm trying to show user inbox on Yii Application but I can't write a right criteria.
Conversation is based on (user_id, recipient_id) ... so there is no conversation table, and my problem is how to sort list conversations without conversations table ?!
and what about if I used yii relations ?!
Table structure:
id (int)
message (text)
user_id (int)
recipient_id (int)
sent_at (int)

and my criteria is:
    $criteria = new CDbCriteria();
    $criteria->condition = "recipient_id=:user_id OR user_id=:user_id";
    // $criteria->group ='user_id';
    // $criteria->select ='*';
    // $criteria->distinct = true;
    $criteria->order = "sent_at ASC";
    $criteria->limit = 5;
    $criteria->params = array(':user_id' => Yii::app()->user->id);
    $model = UserMessage::model()->findAll($criteria);

Output
It's shown all messages recipient

A:

You have to group by the message itself. However, if this is a large text/string field, this will not be an efficient query on the database, and will be very slow. I will urge you to relook at your database structure.
I have implemented something very similar, but I have converted my table to show the relationship between messages.
id (int)
message (text)
user_id (int)
recipient_id (int)
sent_at (int)
reply_to (int) default 0      ;;; I added this field

Using this I can search top level conversations
SELECT * from user_message where reply_to is NULL or reply_to = 0;

Using this scheme, for a new message, the reply_to field will be 0. 
In Yii
$criteria = new CDbCriteria();
$criteria->condition = "reply_to is NULL or reply_to = 0";
$criteria->order = "sent_at ASC";
$criteria->limit = 5;
$model = UserMessage::model()->findAll($criteria);

When viewing a message and creating a reply, set the reply_to code to the value of the upper level. This  allows infinite number of nesting.
MSG : I need help with this question (id = 1, reply_to = 0)
MSG : L Re: I need help with this question (id = 2, reply_to = 1)
MSG : L Re: I need help with this question (id = 3, reply_to = 1)
MSG :   L Re: I need help with this question (id = 4, reply_to = 3)
MSG :   L Re: I need help with this question (id = 5, reply_to = 3)
MSG :      L Re: I need help with this question (id = 8, reply_to = 5)
MSG : L Re: I need help with this question (id = 6, reply_to = 1)
MSG :   L Re: I need help with this question (id = 7, reply_to = 6)

