XF 1.1 Recalculate Trophy Points?

[x7iBiT]

I edited some existing trophies in my Forum. For example, I edited one trophy from 100 likes to 250. Is there a way to recalculate the trophy user titles and the trophy points for all users?

Thanks a lot (and sorry, my english sucks)

 

[Jake Bunce]

Run these two queries:

TRUNCATE TABLE xf_user_trophy;

UPDATE xf_user AS u
SET u.trophy_points = (
	SELECT SUM(t.trophy_points)
	FROM xf_user_trophy AS ut
	LEFT JOIN xf_trophy AS t ON (t.trophy_id = ut.trophy_id)
	WHERE ut.user_id = u.user_id
);

That will make it so everyone gets their trophies awarded again.

Trophies are awarded by this cron, so you may wish to manually run the task after running these queries:

Admin CP -> Tools -> Cron Entries -> Update User Trophies -> Controls: Run

And note that the trophy cron only updates users who have been active in the last 24 hours, so there may be a delay for some users despite manually running the cron.

 

[x7iBiT]

Thank you ver much Jake, this solves the problem completely

 

[craigiri]

Run these two queries:

Trophies are awarded by this cron, so you may wish to manually run the task after running these queries:

Admin CP -> Tools -> Cron Entries -> Update User Trophies -> Controls: Run

And note that the trophy cron only updates users who have been active in the last 24 hours, so there may be a delay for some users despite manually running the cron.

This is an important point - my suggestion is to add it to the manual. I've been going nuts trying to figure out why some users are not being renamed with the default ladder (awarded points)......I've run the cron and waited a day and still no go.....duh, they won't get updated until later....after they login.

It would be nice to have buttons in XF which allowed those of us with lots of extra RAM and processing power to "award points to ALL users" or something similar.....instead of waiting for them to login and then the cron job after that.

 

[Jake Bunce]

This is an important point - my suggestion is to add it to the manual. I've been going nuts trying to figure out why some users are not being renamed with the default ladder (awarded points)......I've run the cron and waited a day and still no go.....duh, they won't get updated until later....after they login.

It would be nice to have buttons in XF which allowed those of us with lots of extra RAM and processing power to "award points to ALL users" or something similar.....instead of waiting for them to login and then the cron job after that.

Try this:

http://xenforo.com/community/thread...cron-running-out-of-memory.31541/#post-362699

It's a new version of the trophy cron which incorporates a memory fix. That should allow you to process all users by commenting out the last_activity condition:

		$conditions = array(
			'user_state' => 'valid',
			'is_banned' => 0,
			// 'last_activity' => array('>', XenForo_Application::$time - 86400)
		);

I referenced a suggestion of mine in that thread to process all users only if the task is run manually, but you can temporarily comment out that line to force it to process all users. And the memory fix should avoid the usual memory problems associated with doing this.

 

[topcat]

sorry to bump an old thread but, is there an option to run a query from the xenforo admincp like in vbulletin or do I need to do it from myphp admin.

I had a go in myphp but im really sure how to do it.

I just keep getting - You have to choose at least one column to display -

trying to run these 2 query's

TRUNCATE TABLE xf_user_trophy;

UPDATE xf_user AS u
SET u.trophy_points = (
SELECT SUM(t.trophy_points)
FROM xf_user_trophy AS ut
LEFT JOIN xf_trophy AS t ON (t.trophy_id = ut.trophy_id)
WHERE ut.user_id = u.user_id
);

 

[topcat]

forget that, sorted it

 

[Alexey.P]

forget that, sorted it

How did you sorted it?

I have no idea where I should run this query ..

 

[Alexey.P]

How do I run that query ?

TRUNCATE TABLE xf_user_trophy;

UPDATE xf_user AS u
SET u.trophy_points = (
SELECT SUM(t.trophy_points)
FROM xf_user_trophy AS ut
LEFT JOIN xf_trophy AS t ON (t.trophy_id = ut.trophy_id)
WHERE ut.user_id = u.user_id
);

 

[Jeremy]

Run it via PhpMyAdmin.

 

[Alexey.P]

Run it via PhpMyAdmin.

Any other way? some thing through ssh maybe?
I don't have phpmyadmin

 

[Jake Bunce]

Probably install it. It's a very useful tool:

http://www.phpmyadmin.net/home_page/index.php

Or you can do it via SSH. Login to your database with this command:

mysql -uDBUSER -pDBPASS DBNAME

Then paste those queries individually at the mysql prompt, making sure to end them with a semicolon.

 

[tab0reqq]

Guys. Could someone help me with this on Xenforo 1.2 ?

I executed those codes. Points are recalulated for most of users, but some of them still have 0 points. Even thou they should have at least 100+.

Code i executed:

TRUNCATE TABLE xf_user_trophy;

UPDATE xf_user AS u
SET u.trophy_points = (
SELECT SUM(t.trophy_points)
FROM xf_user_trophy AS ut
LEFT JOIN xf_trophy AS t ON (t.trophy_id = ut.trophy_id)
WHERE ut.user_id = u.user_id
);

+ this:
ACP - > Tools > rebuild Trophy Point cron task.

Still after almost ~20h some of my users are on zero.

Can i just run some kind of phpmyadmin query just to recalculate Trohpy Points just for one specific user?

 

[Mike]

Don't run the cron task. Use the "Rebuild Trophies" option in Rebuild Caches (1.2.1 only). It will do what you want.

 

[oVR]

This is built into v1.2.2 right?

 

[Jeremy]

Yes. It was added in 1.2.1.

 

[BuildBridges]

Don't run the cron task. Use the "Rebuild Trophies" option in Rebuild Caches (1.2.1 only). It will do what you want.

Does this mean if the entire trophy point system is "redone" it will erase all the old values and recalculate? I filled out some trophy points without really knowing what I was doing and now I need to change the scale.

 

[mitchelled]

OK, I have the same problem. I lost track of time and posts, and added a trophy for 3000 posts and missed one user who already went over that mark. I ran the cron job and the "Rebuild Trophies" option in the cache but the user still isn't awarded the trophy (and they have logged on). What am I missing.

I don't want to re-award the trophies for everyone, just want to fix this one user. Any way to do this in the UI?

 

[Mike]

Rebuilding trophies runs the criteria check against all users. If it doesn't award a trophy to a user (particularly if it does for other users), that would indicate that either the user doesn't meet the criteria or there's something wrong in the DB somewhere (things like this have happened when external systems create accounts). I would certainly double check the criteria though.

 

[mitchelled]

You are correct! PICNIC problem (problem in chair not in computer)! Thanks for making me check and sorry for bothering you! Neglected to set the criteria for one of the trophies!