Query DB for list of recent users who have posted

[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.

🎁 20th Anniversary Give Away

As of January 1, 2020, this website has been around for 20 years! Even though we were not able to celebrate in person, we’re still going to do something special. A huge A&A .org Giveaway! Starting October 2, 2020 and continuing weekly until all the prizes...

Axis & Allies .org Forums (www.axisandallies.org)