So I want code, a plugin or a patch, that will calculate points and put them in qa_usercategorypoints. All the logic should be identical to the logic for populating qa_userpoints except, each user will have multiple records based on the category they interacted with. If they create a question without a category or interact with a question that has no category, then the categoryid for that record will be 0. If they interact with a subcategory, the ancestor categoryid whose parentid is null will be used. Basically, this will allow a user browsing a category or subcategory to see which user is the 'expert' of that category.
Here is the table I want to use:
delimiter $$
CREATE TABLE `qa_usercategorypoints` (
`userid` int(10) unsigned NOT NULL,
`categoryid` int(10) unsigned NOT NULL, /* if they post to a category that has a parentid go down the tree until we get the parent category with a null parentid that categoryid goes here */
`points` int(11) NOT NULL DEFAULT '0',
`qposts` mediumint(9) NOT NULL DEFAULT '0',
`aposts` mediumint(9) NOT NULL DEFAULT '0',
`cposts` mediumint(9) NOT NULL DEFAULT '0',
`aselects` mediumint(9) NOT NULL DEFAULT '0',
`aselecteds` mediumint(9) NOT NULL DEFAULT '0',
`qupvotes` mediumint(9) NOT NULL DEFAULT '0',
`qdownvotes` mediumint(9) NOT NULL DEFAULT '0',
`aupvotes` mediumint(9) NOT NULL DEFAULT '0',
`adownvotes` mediumint(9) NOT NULL DEFAULT '0',
`qvoteds` int(11) NOT NULL DEFAULT '0',
`avoteds` int(11) NOT NULL DEFAULT '0',
`upvoteds` int(11) NOT NULL DEFAULT '0',
`downvoteds` int(11) NOT NULL DEFAULT '0',
`bonus` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`userid`,`categoryid`),
KEY `points` (`points`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
I'm just having trouble wrapping my head around the way the inserts are generated. If I figure out how to implement this before someone answers my question, I'll answer my own question and post the code here. (I just figure that someone else may have already implemented this or can implement it faster than me)
