/**
* returns all the unread threads for a user
* @param integer $id_user
* @return Model_Message
*/
public static function get_unread_threads($id_user)
{
//get the model ;)
$messages = new Model_Message();
//I get first the last message grouped by parent.
//we do this since I need to know if was written, the text and the creation date
$query = DB::select('m1.id_message')->from(array('messages', 'm1'))->join(array('messages', 'm2'), 'LEFT')->on('m1.id_message', '<', 'm2.id_message')->on('m1.id_message_parent', '=', 'm2.id_message_parent')->where('m2.id_message', 'IS', NULL)->where('m1.id_user_to', '=', $id_user)->where('m1.status', '=', Model_Message::STATUS_NOTREAD)->execute();
$ids = $query->as_array();
//filter only if theres results
if (count($ids) > 0) {
$messages->where('id_message', 'IN', $ids);
} else {
$messages->where('id_message', '=', 0);
}
return $messages;
}
/**
* returns all the threads for a user
* @param Model_User $user
* @param integer $status
* @return Model_Message
*/
public static function get_threads($user, $status = NULL)
{
//I get first the last message grouped by parent.
//we do this since I need to know if was written, the text and the creation date
$query = DB::select(DB::expr('MAX(`id_message`) as id_message'))->from('messages');
//filter by status
if ($status !== NULL and is_numeric($status)) {
switch ($status) {
case Model_Message::STATUS_NOTREAD:
$query->where('id_user_to', '=', $user->id_user)->where('status_to', '=', Model_Message::STATUS_NOTREAD);
break;
default:
$query->where_open()->where_open()->where('id_user_to', '=', $user->id_user)->where('status_to', '=', $status)->where_close()->or_where_open()->where('id_user_from', '=', $user->id_user)->where('status_from', '=', $status)->where_close()->where_close();
break;
}
} else {
$query->where_open()->where_open()->where('id_user_to', '=', $user->id_user)->where('status_to', 'in', array(Model_Message::STATUS_NOTREAD, Model_Message::STATUS_READ))->where_close()->or_where_open()->where('id_user_from', '=', $user->id_user)->where('status_from', 'in', array(Model_Message::STATUS_NOTREAD, Model_Message::STATUS_READ))->where_close()->where_close();
}
$query->group_by('id_message_parent')->order_by('id_message');
$ids = $query->execute()->as_array();
//get the model ;)
$messages = new Model_Message();
//filter only if theres results
if (count($ids) > 0) {
$messages->where('id_message', 'IN', $ids);
} else {
$messages->where('id_message', '=', 0);
}
return $messages;
}
/**
* returns all the threads for a user
* @param Model_User $user
* @param integer $status
* @return Model_Message
*/
public static function get_threads($user, $status = NULL)
{
//I get first the last message grouped by parent.
/*SELECT m1.id_message FROM oc2_messages m1
LEFT JOIN oc2_messages m2
ON ( m1.id_message<m2.id_message and m1.id_message_parent=m2.id_message_parent )
WHERE m2.id_message IS NULL AND (m1.id_user_from = 1 OR m1.id_user_to = 1)*/
//I get first the last message grouped by parent.
//we do this since I need to know if was written, the text and the creation date
$query = DB::select('m1.id_message')->from(array('messages', 'm1'))->join(array('messages', 'm2'), 'LEFT')->on('m1.id_message', '<', 'm2.id_message')->on('m1.id_message_parent', '=', 'm2.id_message_parent')->where('m2.id_message', 'IS', NULL);
//filter by status
if ($status !== NULL and is_numeric($status)) {
switch ($status) {
case Model_Message::STATUS_NOTREAD:
$query->where('m1.id_user_to', '=', $user->id_user)->where('m1.status_to', '=', Model_Message::STATUS_NOTREAD);
break;
default:
$query->where_open()->where_open()->where('m1.id_user_to', '=', $user->id_user)->where('m1.status_to', '=', $status)->where_close()->or_where_open()->where('m1.id_user_from', '=', $user->id_user)->where('m1.status_from', '=', $status)->where_close()->where_close();
break;
}
} else {
$query->where_open()->where_open()->where('m1.id_user_to', '=', $user->id_user)->where('m1.status_to', 'in', array(Model_Message::STATUS_NOTREAD, Model_Message::STATUS_READ))->where_close()->or_where_open()->where('m1.id_user_from', '=', $user->id_user)->where('m1.status_from', 'in', array(Model_Message::STATUS_NOTREAD, Model_Message::STATUS_READ))->where_close()->where_close();
}
$ids = $query->execute()->as_array();
//get the model ;)
$messages = new Model_Message();
//filter only if theres results
if (count($ids) > 0) {
$messages->where('id_message', 'IN', $ids);
} else {
$messages->where('id_message', '=', 0);
}
return $messages;
}
