1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'XenForo Questions and Support' started by Gene, Oct 2, 2013.

  1. 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: Oct 3, 2013
  2. JulianD

    JulianD Well-Known Member

    Wait....
     
  3. JulianD

    JulianD Well-Known Member

    Try this query:

    PHP:
    select u.usernamecount(p.post_id) as post_countsum(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.usernamecount(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.
     
  4. Gene

    Gene Member

    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: Oct 3, 2013
  5. JulianD

    JulianD Well-Known Member

    Yeah, I'm not taking into consideration private forums, and / or moderated messages.
     
  6. Gene

    Gene Member

    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.
     
  7. Tracy Perry

    Tracy Perry Well-Known Member

    Private conversations?
     
  8. Gene

    Gene Member

    Nope, I explicitly require content_type to be a post or profile_post.
     
    Last edited: Oct 3, 2013
  9. Gene

    Gene Member

    Last edited: Oct 3, 2013

Share This Page