sorting - Mongodb conversations collection structure (how to get the count of new messages) -

sorting - Mongodb conversations collection structure (how to get the count of new messages) -

i'm developing conversation box site construction of documents following:

{ '_id' : {'$id' :'507f1f77bcf86cd799439011'} 'user_a' : { 'user_id' : {'$id' :'54304264e77cc5a1670cb318'}, 'updated' : '2014-11-01 19:56:09.000z' }, 'user_b' : { 'user_id' : {'$id' :'53efcb8fe77cc56550a049d0'}, 'updated' : '2014-11-01 19:56:09.000z' }, 'updated' : '2014-11-01 19:56:09.000z', 'messages' : [ { 'user_id' : {'$id' :'54304264e77cc5a1670cb318'}, 'text' : "some text goes here..." }, { 'user_id' : {'$id' :'53efcb8fe77cc56550a049d0'}, 'text' : "some text goes here..." }, }

i'm looking way to find number of new messages user got since lastly checked. when user check message set 'updated' time of user time checked message, , when user submit new message update 'updated' property of document.

i've been playing different aggregate combinations find user updated date of user older updated date of document, there way that, or there improve way design construction of documents accomplish that?

update

i modified design based on max noel reply - adding date/timestamp each of messages, new construction looks this

{ '_id' : {'$id' :'507f1f77bcf86cd799439011'} 'user_a' : {'$id' :'54304264e77cc5a1670cb318'}, 'user_b' : {'$id' :'53efcb8fe77cc56550a049d0'}, 'updated' : '2014-11-01 19:56:09.000z', 'messages' : [ { 'user_id' : {'$id' :'54304264e77cc5a1670cb318'}, 'text' : "some text goes here...", 'added' : '2014-11-01 19:56:09.000z', 'read' : false, }, { 'user_id' : {'$id' :'53efcb8fe77cc56550a049d0'}, 'text' : "some text goes here...", 'added' : '2014-11-01 19:56:09.000z', 'read' : '2014-11-01 22:56:09.000z', }, }

using next aggregate function got count of messages has user_id not similar 1 i'm looking, , read status of false.

$found = $db->collection->aggregate( array( array( '$match' => array( '$or' => array( array('user_a' => $user_id), array('user_b' => $user_id) ) ) ), array( '$project' => array( 'm' => '$messages', '_id' => 0) ), array( '$unwind' => '$m'), array( '$match' => array( 'm.user_id' => array( '$ne' => $user_id ), 'm.read' => false ) ), array( '$group' => array( '_id' => 1, 'count' => array( '$sum' => 1 ) ) ) ) );

if time @ user lastly checked inbox trigger, means messages must have date attribute (datetime, really, same thing). way, can query messages date greater lastly update time, , if anything, means there messages user didn't see.

in addition, if create index on messages.date (realistically it'll compound index messages.date lastly component), means can utilize both query proper and sorting messages, means more performance.

mongodb sorting aggregate-functions mongodb-query aggregation-framework