XF 1.2 SQL queries to get total likes, posts, etc. for all users

Gene

Gene

Member
A user of ours is running a contest this month, awarding a pretty serious prize to the user with the most likes and posts in the 30 days starting 8:00PM EDT tonight. Currently, he is asking users to self-report using a screenshot of their profile. There has got to be a better way, especially since I can basically do this already in the "notable users" area. What I'm basically asking is if there's any way for me to run a "notable users" report for ALL users, not just the top 20. (Or if there's a more elegant way to get a report from the database or something, but really, we don't have a ton of users so it doesn't matter much.)
 
Last edited:
Sort by date Sort by votes
Try this query:

PHP: 
select u.username, count(p.post_id) as post_count, sum(p.likes) as like_count
from xf_post p
inner join xf_user u on (p.user_id = u.user_id)
where p.message_state = 'visible'
and p.post_date > (unix_timestamp()-2592000) and p.post_date < unix_timestamp()
group by p.user_id
order by post_count desc

It will count all the post made by users in the last 30 days and how many likes those posts got. However, an user could have received likes in the last 30 days from posts way more older than 30 days. If you want to count how many likes the user received in the last 30 days, run this query:

PHP: 
select u.username, count(like_id) as like_count
from xf_liked_content l
inner join xf_user u on (u.user_id = l.content_user_id)
where like_date > (unix_timestamp()-2592000) and like_date < unix_timestamp()
group by content_user_id
order by like_count desc


Of course, I could have made mistakes in my queries, you should double check first if the results you get from those queries are correct according to the rules of your contest. I hope it helps you :)

Edit: The time range is dynamic which means you have to run this query at the exact time when the contest ends. If you run it before or after, the results may change due to the time range being dynamic. Let me know the exact time range and I could modify the query to match the range exactly.
 
Upvote 0 Downvote
Thanks! I ended up with these queries. The likes can be on posts from any time.

Started with these queries:

The total likes received before 8PM EDT tonight:
Code: 
select u.username, count(like_id) as like_count
from xf_liked_content l
inner join xf_user u on (u.user_id = l.content_user_id)
where like_date < (1380758400)
group by content_user_id
order by like_count desc

Number of likes received after 8PM EDT tonight, but before 8PM EDT on 11/2:
Code: 
select u.username, count(like_id) as like_count
from xf_liked_content l
inner join xf_user u on (u.user_id = l.content_user_id)
where like_date > (1380758400) and like_date < (1383436800)
group by content_user_id
order by like_count desc

Because I have conversation essentials installed, these queries returned likes on posts, profile posts, AND conversation messages, resulting in way more likes than XF is reporting.

So to fix that, I specify the content types (note that this query doesn't specify a timestamp; at this point I'm trying to get the correct counts):
Code: 
select u.username, count(like_id) as like_count
from xf_liked_content l
inner join xf_user u on (u.user_id = l.content_user_id)
inner join xf_post p on (p.post_id = l.content_id)
where (content_type = "post" or content_type = "profile_post") and (message_state = "visible")
group by content_user_id
order by like_count desc

For some reason, this query gives me a small but consistent number of extra likes for many of my users, but not all of my users. I'm not sure what these likes are, but XF does not include them in the official 'total likes received' number reported in profiles and on the notable members list.

I even tried excluding likes from banned users. Still produces a handful of extra likes for many users:
Code: 
select u.username, count(like_id) as like_count
from xf_liked_content l
inner join xf_user u on (u.user_id = l.content_user_id)
inner join xf_user v on (v.user_id = l.like_user_id)
inner join xf_post p on (p.post_id = l.content_id)
where (content_type = "post" OR content_type = "profile_post") and (message_state = "visible") and v.is_banned = 0
group by content_user_id
order by like_count desc

I also tried doing it backwards: Counting each user's posts and profile posts, summing the likes on those, and adding the two numbers together. Same exact results. I am totally stumped.
 
Last edited:
Upvote 0 Downvote
JulianD said:
Yeah, I'm not taking into consideration private forums, and / or moderated messages.
Click to expand...
It's neither of these. There is some kind of regular, unmoderated, publicly-visible post that XF doesn't count when it's calculating total likes, and I can't figure it out. The strange thing is that I don't have any of these mystery likes, while some of my users do - but even my most-liked user, with 1600 likes, only has around 35 of these mystery likes. I'm counting both post and profile_post in the query now, so I'm not sure what it is.
 
Upvote 0 Downvote
Gene said:
It's neither of these. There is some kind of regular, unmoderated, publicly-visible post that XF doesn't count when it's calculating total likes, and I can't figure it out. The strange thing is that I don't have any of these mystery likes, while some of my users do - but even my most-liked user, with 1600 likes, only has around 35 of these mystery likes. I'm counting both post and profile_post in the query now, so I'm not sure what it is.
Click to expand...
Private conversations?
 
Upvote 0 Downvote
You must log in or register to reply here.
Back
Top Bottom