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

XF 1.5 SQL Query Help

Discussion in 'XenForo Questions and Support' started by Ark Royal, May 29, 2016.

Tags:
  1. Ark Royal

    Ark Royal Active Member

    Can anyone help me and provide me with an SQL Query that will allow me to see when members of a particular user group last made a post?

    Many Thanks
     
  2. Mouth

    Mouth Well-Known Member

    select username, from_unixtime(last_activity) from xf_user where find_in_set('xx',secondary_group_ids) <> 0 order by last_activity desc;

    ..... replace xx with the usergroup id you want to check against
     
  3. Ark Royal

    Ark Royal Active Member

    Thanks for the quick response.
    Is last_activity when a post was actually made or could it be just viewing?
     
  4. Mouth

    Mouth Well-Known Member

    The latter ... activity includes logon, profile posts, etc.
     
  5. Ark Royal

    Ark Royal Active Member

    Any advice how to get the date of the last post made by the user?
     
  6. TJ Adams

    TJ Adams Member

    If the users in question have the desired usergroup as a secondary_group_id... this example is selecting members in user group 9 and showing their last post time:

    Code:
    SELECT user.username, MAX(post.post_date) AS last_post_date FROM xf_user AS user
    LEFT JOIN xf_post AS post ON(post.user_id = user.user_id)
    WHERE FIND_IN_SET(9, user.secondary_group_ids) GROUP BY post.user_id ORDER BY last_post_date DESC;
    
    This is fairly basic. It's not checking to see if that last post is soft-deleted or moderated, for example, and the date is output as a UNIX time format value.
     
    Ark Royal and Mouth like this.
  7. Ark Royal

    Ark Royal Active Member

Share This Page