Query DB for list of recent users who have posted

[djensen47]

How would I query the DB, or write some Javascript, to get a list of users who have posted a topic or reply in the past 21 (or whatever number) days?

Is the "schema" documented anywhere yet? It's been a while since I last looked.

[djensen47]

Looks like a table scan of millions and millions of documents if I try to sort on timestamp.

[baris]

db.objects.find({
  _key: /^topic:\d+$/, 
  timestamp: {$gte: <replace_with_timestamp>}
}).sort({ timestamp: -1});

This will give you topics created in the past X days, adjust timestamp as necessary. You can do it for posts as well and grab the uids from the returned results.

[djensen47]

@baris My query is timing out, probably due to a lack of an index on timestamp. I tried an aggregate and it's not much better.

[baris]

What does this return?

db.objects.count({
  _key: /^topic:\d+$/, 
  timestamp: {$gte: 1601510400000}
});

[djensen47]

@baris Topic queries seem to run fine. It's when I query on post; it times out.

But to answer your question 6 is the response.

But this times out after 120s but only when the timestamp query is in there.

db.objects.count({
  _key: /^post:\d+$/, 
  timestamp: {$gte: 1601510400000}
});

[djensen47]

This returned quickly:

db.objects.aggregate([{
    $match: {_key: /^post:\d+$/}
},{
    $group: { _id: null, count: { $sum: 1 } }
}]);

{
    "_id" : null,
    "count" : 1434648.0
}

[baris]

Ahh yeah having 1.4million posts would do that. Try this, put the following in a file called query.js in your nodebb root folder.

var postKeys = db.objects.find({
  _key: "posts:pid",
  score: { $gte: 1601510400000 }
}).map(function (post) {
  return 'post:' + post.value;
});

var posts = db.objects.find({
  _key: { $in: postKeys }
}).map(function (p) {
  return { uid: p.uid, pid: p.pid };
});

printjson(posts);

Then you can run it with mongo localhost:27017/<database_name> query.js

[djensen47]

@baris Thank you! With the knowledge of that query in mind, I made this aggregate:

I usually prefer aggregates for complex queries like this. Plus I can also get a count of the posts by user too.

db.objects.aggregate([{
    $match: {_key: "posts:pid", score: { $gte:  d }}
},{
    $addFields: {"pk": {$concat: ["post:", "$value"]}}
},{
    $lookup: {
        from: "objects",
        let: { "postKey": "$pk"},
        pipeline: [
            {$match: {$expr: {$eq: ["$_key", "$$postKey"]}}},
        ],
        as: "post"
    }
},{
    $project: {post: {$arrayElemAt: ["$post", 0]}}     
}, {
    $project: {_id: 0, uid: "$post.uid"}
},{
    $group: {_id: "$uid", count: {$sum: 1}}
}]);

[julian]

@djensen47 btw welcome back hope the A&A forum is doing well

[djensen47]

@julian Thanks, it's going great. Doing a give away, thus the need for some special queries. If the team here wants to participate, just jump over and say hello. https://www.axisandallies.org/forums/topic/35780/20th-anniversary-give-away