Query DB for list of recent users who have posted


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


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

  • Admin NodeBB

    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.


  • @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.

  • Admin NodeBB

    What does this return?

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

  • @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}
    });
    

  • This returned quickly:

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

    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


  • @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}}
    }]);
    
  • GNU/Linux Admin

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


  • @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

Suggested Topics

| |