searching beyond the scope of Enhanced Search?

[maxd]

hello all. i’m looking for guidance on situations like these:

scenario 1: which users from groups X, Y and Z (but not groups A, B or C) that have made more than 100 posts in the last N days.
scenario 2: which users have sent more than X Direct Messages in the past N days?
scenario 3: which forums/subforums have had less than X posts in the past N days.

assuming you want to do these kinds of searches on a large forum — approx 100k threads — on a fairly regular basis, what should your tool(s) of choice be here?

 

[chillibear]

scenario 2: which users have sent more than X Direct Messages in the past N days?

That one is easy enough with an simple SQL query such as:

SELECT username, user_id, COUNT(*) AS message_count FROM xf_conversation_message WHERE message_date >= UNIX_TIMESTAMP(NOW() - INTERVAL 10 DAY) GROUP BY user_id HAVING COUNT(*) > 5 ORDER BY message_count DESC;

In that case a 10 day period and having more than 5 messages. If the table is large then adding an extra composite index over message_date and user_id might help.

 

[Alpha1]

Install @Xon 's User and Search addons ( including Criteria) and then you can do so.