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 Query Fun - Combine Two Queries - Posts & Likes 30 Days

Discussion in 'XenForo Questions and Support' started by ProCom, Dec 12, 2013.

  1. ProCom

    ProCom Active Member

    I'd love to have these two queries (that I've scavenged together from great threads here) into one query.

    Basically: Number of posts and number of likes per user for the past 30 days:

    UserID, Username, #_Posts, #_Likes_Received

    Here is the first query:

    SELECT u.username AS username, COUNT(*) AS totalPosts
    FROM xf_post AS p
    LEFT JOIN xf_user AS u ON (u.user_id = p.user_id)
    GROUP BY p.user_id
    ORDER BY totalPosts
    ... and the second:

    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(DATE_ADD(NOW(), INTERVAL -30 DAY))
    group by content_user_id
    order by like_count desc
    Both of those work individually for the data over the past 30 days, but I'd love to combine both data points on one line per user.

    Thanks in advance you master queriers!!!
  2. Luke F

    Luke F Well-Known Member

    select user.username as username, ifnull(pc.post_count, 0) as post_count, ifnull(lc.like_count, 0) as like_count
    from xf_user user
    left join (
        select count(*) as post_count, p.*
        from xf_post as p
        where post_date > unix_timestamp(date_add(now(), interval -30 day))
        group by p.user_id
    ) as pc on (pc.user_id = user.user_id)
    left join (
        select count(like_id) as like_count, l.*
        from xf_liked_content l
        where like_date > unix_timestamp(date_add(now(), interval -30 day))
        group by content_user_id
    ) as lc on (lc.content_user_id = user.user_id)
    where user.message_count > 0 and user.like_count > 0 and (pc.post_count > 0 or lc.like_count > 0)
    order by post_count desc
    Probably not very efficient, also making the assumptions you don't want 0 values and you want it ordered by post count
    ProCom likes this.
  3. ProCom

    ProCom Active Member

    Wow, looks like it's working perfectly... GREAT JOB!

    I'm not too worried about how efficient the queries like this are since I only run them once in a while when pulling user updates on who to recognize.

    Thanks again!

Share This Page