I'm working on a messaging system with two tables and another table with the users information.  

A conversation can be between 2 or more users. Every conversation has a UID and every messages exchanged between users are labeled with that conversation UID. 

Here are the tables :

`conversation_list` : every row in this table links the `user_id` and the `conversation_id`, it also contains the last time the user viewed the conversation.

```
`id`                 -> unique ID, autoincremented
`user_id`            -> This contains the user associated with the conversation.
`conversation_id`    -> This contains the UID of the conversation
`date_lastView`      -> This field has the time that the user viewed the conversation last

```

`conversation_messages` : every row in this table contains a message 

```
`id`                 -> unique ID, autoincremented
`user_id`            -> This contains the user that sent the message.
`conversation_id`    -> This contains the UID of the conversation
`date_created`       -> This contains the time when the message was posted
`message`            -> This contains the message

```

`users` : every row in this table contains a user 

```
`User_ID`            -> UID of the user
`FirstName`          -> This contains the first name of the user
`LastName`           -> This contains the last name of the user

```

I already have a SQL query to get the last message of every conversation. Here it is : 

```
SELECT *
FROM conversation_messages AS m

JOIN
  (SELECT mx.conversation_id,
          MAX(mx.date_created) AS MaxTime
   FROM conversation_messages AS mx
   GROUP BY mx.conversation_id) AS mx ON m.conversation_id = mx.conversation_id
AND m.date_created = mx.MaxTime

JOIN
  (SELECT mu.conversation_id
   FROM conversation_list AS mu
   WHERE mu.user_id = :USER_ID_CONNECTED
   GROUP BY mu.conversation_id) AS mux ON m.conversation_id = mux.conversation_id

JOIN conversation_list AS mu ON m.conversation_id = mu.conversation_id

GROUP BY mu.conversation_id
ORDER BY m.date_created DESC

```

I now would like to add to this perfectly working query the ability to return:

* The number of unread messages for each conversation (count of all messages with the `date_creaded` bigger then `date_lastView` of the logged in user)
* An array containing the `User_ID` of every user in each conversation and sorted by when they last posted a message in the conversation.
* With the same idea of the last array but with the `FirstName` and `LastName` of the user.

I tried a few things but I was really unsuccessful, so I'm now asking the SO community for its precious help.

All this can only display conversations where the logged in user takes part in.

It it helps, I created a [SQLFiddle](http://sqlfiddle.com/#!2/c7306/2)