Can I do this with a single query?

[JamesAB]

I'm dealing with a forum running vBulletin 3.6.8.

We would like to be able to move every user from usergroup 2 that has uploaded more than 10 MB of attachments into usergroup 17.

Would this be possible with a single mySQL query? Or do I need to look into a more complex approach?

Basically, I'm looking for a simple way to promote users based on their attachment statistics but I am not a mySQL wiz. Any help would be appreciated.

Thanks,
James

[Lats]

Try this...

UPDATE
    user,
    (SELECT
        userid
     FROM
        attachment
     GROUP BY
        userid
     HAVING
        sum(filesize) > 10000000)
AS
    user2
SET
    user.usergroupid = 17
WHERE
    user2.userid = user.userid
AND
    user.usergroupid = 2

[JamesAB]

Thank you Lats.

I'll try this on my test forum.

[JamesAB]

I finally got to test this on my test forum and it seemed to work just as I hoped.
Lats, I can't thank you enough for helping me get started on this.

I'm really exited about having the possiblity to do promotions based on attachment stats.


I have a couple more questions if you don't mind.

1. How would I do the same thing, but just make the user an additional user of a usergroup rather than change their primary usergroup?
2. I'm assuming this is checking all the user's attachments. Some could be in deleted threads or posts, correct? If so, is it possible to only count attachments that are in visible threads & posts?

Thanks for your help,
James

[Lats]

#1 is a small change to the original...

UPDATE
    user,
    (SELECT
        userid
     FROM
        attachment
     GROUP BY
        userid
     HAVING
        sum(filesize) > 10000000)
AS
    user2
SET
    user.membergroupids = 17
WHERE
    user2.userid = user.userid
AND
    user.usergroupid = 2

#2 The attachment table keeps track of deletions, so it's only counting what's visible.

[Crimson Shadow]

You might want to change "user.membergroupids = 17" to:

user.membergroupids = IF(user.membergroupids = '', '17', CONCAT(user.membergroupids, ',17'))

This way it won't overwrite what's already there. This also isn't perfect, though, as it'll re-add 17 if it's already in the list. I don't think it'll have any serious implications, however.

[Lats]

Yes, good call although from reading the requirement I don't believe James has anybody in usergroup 17 - could be wrong though

[JamesAB]

You might want to change "user.membergroupids = 17" to:

user.membergroupids = IF(user.membergroupids = '', '17', CONCAT(user.membergroupids, ',17'))

This way it won't overwrite what's already there. This also isn't perfect, though, as it'll re-add 17 if it's already in the list. I don't think it'll have any serious implications, however.

Thanks for the suggestion Crimson Shadow.

I'm still learning, but I think I understand the change you suggested now.

This will first check if the user is a member of any additional usergroups. If not, it will set them to be an additional user of usergroup 17.

If they do have any additional usergroups already, it will get the list of usergroup ID's and then add "17" to the end of the list.

Correct?

If I understand correctly, the downfall you mentioned is the fact that if this query is run and a member already has "17" in their list of additional usergroup ID's, the number 17 could get added to the list again. So in theory that user's user.membergroupids sting could end up with "17" multiple times for each time the query is run.

Am I understanding this right?

Thanks for your help,
James

[Crimson Shadow]

Yes, that's correct. As for making it not add the usergroup ID twice, you could add in an ugly nested IF with an INSTR to check if '17' exists, as long as you don't have any usergroup IDs like 117 or 171.

user.membergroupids = 
IF(user.membergroupids = '', '17', 
    IF(INSTR(user.membergroupids, '17') > 0, user.membergroupids,
        CONCAT(user.membergroupids, ',17')
   )
)

Kind of hard to read, so here's what it's doing in pseudocode:

if (user.membergroupids is empty) {
    user.membergroupids = '17'
}
else {
    if (user.membergroupids contains '17') {
        user.membergroupids = user.membergroupids // so it doesn't change
    }
    else {
        user.membergroupids = user.membergroupids + ',17'
    }
}

[Lats]

Very nice CS, and I don't know what I was thinking @ post #7 - completely disregarded who's in membergroupids already.

Upon review and considering what James mentioned in point 2 @ post #4, I guess a full query would remove a member from membergroupids 17 first if they failed the 10 MB test, then proceed with any additions.

[JamesAB]

Thanks for the detailed help Crimson Shadow & Lats. I'll try this on my test server tonight.

If I were going to develop something like this into a promotion system based on attachments I would need to move users in and out (if their attachments, or posts with attachments get deleted) of the new usergroup.

I understand the latest query for making a member an additional member of a usergroup.

What is the best way to remove a member from an additional usergroup, while still preserving any other additional usergroups they may belong to?

I can imagine 2 scenarios:

1. They only have the one value for membergroupids, 17.
2. They have a list of values for membergroupids, like 13,15,17 or 13,15,17,23,25.

In the second scenario, wouldn't you need to remove either the string 17 or possibly the comma as well like 17, ?

Thanks,
James

[Crimson Shadow]

Looks like the fastest way is to use a few REPLACE calls. First replace removes "17,", next one removes ",17", and the final one removes a plain "17".

user.membergroupids = REPLACE(REPLACE(REPLACE(user.membergroupids, '17,', ''), ',17', ''), '17', '')

[JamesAB]

Once again, thanks for everyone's help so far.

I believe I found a flaw in my logic so far. When a moderator soft deletes a post but keeps the attachments, the attachments stay in the table and still gets counted in the above queries.

I looked at the post table and saw that deleted posts have visible set to '2'. Normal posts have visible set to '1'.

The attachment table also has visible column, but it is always set at '1'.

Is there a way to use the postid of the attachment to check against the post table to make sure the post is visible before calculating it in the sum of a user's attachments?

Conceptually I'm talking about somehow adding "WHERE post.visible = 1", but I'm stumped for how to translate this into the above query.

Thanks,
James

[Lats]

Adding on from post #5...

UPDATE
    user,
    (SELECT
        attachment.userid
     FROM
        attachment
     LEFT JOIN 
       post 
     ON
       attachment.postid = post.postid
     WHERE 
       post.visible = 1
     GROUP BY
        userid
     HAVING
        sum(filesize) > 10000000)
AS
    user2
SET
    user.membergroupids = 17
WHERE
    user2.userid = user.userid
AND
    user.usergroupid = 2

[JamesAB]

Thanks Lats. That last query worked as it should, however I found one more quirk.

The post.visible variable is NOT set to '2' if it is the first post of a thread. It stays set as '1' even if the thread is deleted.

The only option (that I can see) now is to check whether the thread is deleted or not by looking up the thread.visible setting based on the post.threadid

Here's what I have come up with:

 
UPDATE 
     user, 
     (SELECT
          attachment.userid
     FROM
          attachment AS attachment
     LEFT JOIN post AS post ON (post.postid = attachment.postid)
     LEFT JOIN thread AS thread ON (post.threadid = thread.threadid)
     WHERE 
          thread.visible = 1 AND post.visible = 1
     GROUP BY
          userid
     HAVING
          sum(filesize) > 10000000)
AS
     user2
SET
     user.usergroupid = 17
WHERE
     user2.userid = vb3_user.userid
AND
     user.usergroupid = 2

I'm guessing it is working as I want it to because this query moves 1,859 members versus Lats query in post #14 which moved 1861 members. There were probably 2 members with deleted threads that put them under the limit.

So I have to ask, is this the best way to do this? Or is there a better way to optimize this?

My new query takes about 7.5 seconds to run.
The query in post #14 takes just under 5 seconds to run.

Thanks,
James