title: Mongodb retrieve conversation from messages
body: <p>In my mongodb database i have a collection for messages that i use for sending private messages from user to user. I want to retrieve conversations of a user.
  I have this data model:</p>
  
  <p><strong>Updated json</strong></p>
  
  <pre><code>{
     "_id" : ObjectId("577f81fde4501ba208b0e470"),
     "text" : "This is a message",
     "sender_profile_id" : ObjectId("577e69b0794f49d962f10e85"),
     "receiver_profile_id" : ObjectId("577e57393db745060b532000"),
  },    
  {
     "_id" : ObjectId("577f81fde4501ba208b0e470"),
     "text" : "This is a message",
     "sender_profile_id" : ObjectId("577e57393db745060b532000"),
     "receiver_profile_id" : ObjectId("577e69b0794f49d962f10e85"),
  },    
  {
     "_id" : ObjectId("577f81fde4501ba208b0e470"),
     "text" : "This is a message",
     "sender_profile_id" : ObjectId("577e57393db745060b532000"),
     "receiver_profile_id" : ObjectId("577e69b0794f49d962f10e82"),
  },  
  {
     "_id" : ObjectId("577f81fde4501ba208b0e470"),
     "text" : "This is a message",
     "sender_profile_id" : ObjectId("577e69b0794f49d962f10e82"),
     "receiver_profile_id" : ObjectId("577e57393db745060b532000"),
  },    
  </code></pre>
  
  <p>With this json in theory i have 2 conversation for user ObjectId("577e57393db745060b532000"). How can i retrieve them?</p>
  
  <p><strong>Updated solution</strong></p>
  
  <p>I resolved with this:</p>
  
  <pre><code>var selector = {};
  selector['$or'] = [
    {"sender_profile_id" : ObjectId(decoded.profile._id)},
    {"receiver_profile_id" : ObjectId(decoded.profile._id)}
  ];
  
  selector['_acl'] = {$exists : true};
  selector['_acl.' + decoded.profile._id] = {$exists : true};
  selector['_acl.' + decoded.profile._id + '.read'] = true;
  
  var query = {
    $match : selector
  };
  
  var group = {
    $group: {
      _id: "$_id",
      text : { $last: '$text' },
      sender_profile_id : { $last: '$sender_profile_id' },
      receiver_profile_id : { $last: '$receiver_profile_id' },
      receiver : {$addToSet : '$receiver_profile_id'},
      sender: {$addToSet : '$sender_profile_id'},
      created_at : { $last: '$created_at' },
      updated_at : { $last: '$updated_at' },
      count: { $sum: 1 }
    }
  };
  
  var project = {
    $project: {
      _id: "$_id",
      text: "$text",
      sender_profile_id: '$sender_profile_id',
      receiver_profile_id: '$receiver_profile_id',
      created_at : '$created_at',
      updated_at : '$updated_at',
      participants : {
        $cond: {
          if: {
            $gte: ["$sender", "$receiver"] },
            then: { "$setUnion": ["$receiver", "$sender"] },
            else:  { "$setUnion": ["$sender", "$receiver"] }
          }
        }
      }
    };
  
    var group2 =   {
      $group: {
        _id: "$participants",
        text : { $first: '$text' },
        sender_profile_id : { $first: '$sender_profile_id' },
        receiver_profile_id : { $first: '$receiver_profile_id' },
        created_at : { $first: '$created_at' },
        updated_at : { $first: '$updated_at' },
        count: { $sum: 1 }
      }
    };
  
  var project2 = {
    $project: {
      _id : 0,
      participants : "$_id",
      text: "$text",
      interlocutor_profile_id: {
        $cond: {
          if: {"$eq": [ "$receiver_profile_id", ObjectId(decoded.profile._id)]},
          then: '$sender_profile_id',
          else: '$receiver_profile_id'
      }
    },
    created_at : 1,
    updated_at : 1,
    count: 1
    }
  }
  
  var sort = {
    $sort: {
        "created_at": -1
      }
  }
  
  var sort2 = {
    $sort: {
        "created_at": -1
      }
  }
  
  db.collection('message').aggregate(query, group, project, sort, group2, project2, sort2, function(error, result)
  </code></pre>