XF 2.1 Count posts in this month, last month, x days

[Robert9]

For XF1 we had an add-on called Rolling message counter

[WMTech] 5 Rolling Message Counter

Description: Did you ever want to know which members have not written for a long time and which members have been very active in your forum recently? This add-on is the solution for you! Rolling Message Counter is an add-on that adds five true...

xenforo.com

For XF2 we have for example Most Posts

Most posts

Premium upgrade: This XF2 add-on along with the entire collection can be purchased for $35.00 USD. Your Premium upgrade will allow you to download as many XF2 add-ons as you like for one year. Please see the entire collection located in the...

xenforo.com

Today i want to make my own version of this and ask for ideas what could/should be implemented.

Possible functions:

• count posts this month (real month, now November)
• last month (October)
• last two month (now Nov/Oct)

• last 30, 60, 90 days
• last 80, 360 days

Andy uses this select

        // run query
        $results = $db->fetchAll("
        SELECT xf_post.user_id,
        COUNT(*) AS mostPostsCount
        FROM xf_post
        INNER JOIN xf_user ON xf_user.user_id = xf_post.user_id
        WHERE xf_post.post_date >= ?
        AND xf_user.is_banned != 1
        GROUP BY xf_post.user_id
        ORDER BY mostPostsCount DESC
        LIMIT ?  
        ", array($timestamp, $limit));

Is this the right way? Is there an better way?
(visible is also missing)


2. Next step could be to show this at the users page

3. And i want to number of count be usable inside promotion.

If you have more ideas or know another add-on like this, let me know, please.

 

[Ozzy47]

The code you show should be using the finder, not directly using database queries.

 

[Robert9]

Yes, shure. This is code from AndyB.
At the moment i am not shure 1) which other functions could be interesting and 2) how to fetch/save the data.

Should i query all users with posts done in the last x days?
Or maybe just count new posts itself? We have a message_count for all and i could also add one, two, three counters for the last one, two, three month.

Also i could reduce the query.

• not online the last x days
• less than x posts

I just dont know what makes sense (sense in a small board, sense in a large board);
if i use custom user fields, i dont have to care for anything else for the promotion; if i use new xf_user.fields or an own table, i have to care. While watching an add-on for extend the user criteria this is no big story, i guess.

 

[021]

The code you show should be using the finder, not directly using database queries.

.yes, of course. But Finder does not support GROUP BY, and loading several thousand posts in order to later group them in Collection is such a solution)
Also Finder does not support ORDER BY COUNT

 

[TheSkullKid]

loading several thousand posts in order to later group them in Collection is such a solution)

could you explain this a little bit more or add an example please.

 

[021]

could you explain this a little bit more or add an example please.

To be honest, I don't even understand what this is about. There were clearly other messages in the thread