Question 117316 in Mongodb Question posted in
The official documentation can be found here.

MongoDB – Count unread messages

I try to count unread messages for a user.

On my model, I have a property, LastMessageDate that contains the date on the last created message in the group chat. I have also a Members property (list) that contains the members in the group chat. Each member has the UserId and LastReadDate properties. The LastReadDate is updated when the user writes a new message in the group chat or when the user loads messages from the group chat.

Now I want to count the number of chats where a specific user has unread messages (The messages are stored in another collection).
I try this:

var db = GetGroupCollection();

var filter = Builders<ChatGroup>.Filter.Where(p => p.Members.Any(m => m.UserId == userId && m.LastReadDate < p.LastMessageDate));
return await db.CountDocumentsAsync(filter);

But I receive the following error:

The LINQ expression: {document}{Members}.Where((({document}{UserId} ==
730ddbc7-5d03-4060-b9ef-2913d0b1d7db) AndAlso
({document}{LastReadDate} < {document}{LastMessageDate}))) has the
member "p" which can not be used to build a correct MongoDB query.

What should I do? Is there a better solution?

Tags:

2

Answers


  1. 0

    When you want to query a nested list of a document, ElemMatch is your solution,
    Try

    var filter = builder.ElemMatch(o => o.Members,m => m.UserId == userId && m.LastReadDate < p.LastMessageDate);
    Login or Signup to reply.
  2. 0

    Based on the provided data in the comment, I think the aggregation query is required to achieve the outcome.

    1. $set – Set Members field

      1.1. $filter – With Members array as input, filter the document(s) with matching the current document’s UserId and LastMessageDate is greater than ($gt) the current document’s LastReadDate.

    2. $match – Filter the document with Members is not an empty array.

    db.groups.aggregate([
  {
    "$set": {
      Members: {
        $filter: {
          input: "$Members",
          cond: {
            $and: [
              {
                $eq: [
                  "$$this.UserId",
                  1
                ]
              },
              {
                $gt: [
                  "$LastMessageDate",
                  "$$this.LastReadDate"
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      Members: {
        $ne: []
      }
    }
  }
])

    Sample Mongo Playground

    For C# syntax, either you can directly provide the query as a string or convert the query to BsonDocument syntax.

    Note that the query above will return the array of documents, hence you will need to use System.Linq to count the returned document(s).

    using System.Linq;

var pipeline = new BsonDocument[]
{
    new BsonDocument("$set", 
        new BsonDocument("Members", 
            new BsonDocument("$filter", 
                new BsonDocument
                { 
                    { "input", "$Members" },
                    { "cond", new BsonDocument
                        (
                            "$and", new BsonArray
                            {
                                new BsonDocument("$eq", 
                                    new BsonArray { "$$this.UserId", userId }),
                                new BsonDocument("$gt",
                                    new BsonArray { "$LastMessageDate", "$$this.LastReadDate" })
                            }
                        )
                    }
                }
            )
        )
    ),
    new BsonDocument("$match",
        new BsonDocument("Members",
            new BsonDocument("$ne", new BsonArray())))

};

var db = GetGroupCollection();

return (await db.AggregateAsync<BsonDocument>(pipeline))
    .ToList()
    .Count;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search