Can I get out the comments number per user?

[bcai]

Hi admin,



I need to know how many comments posted per user basis during a period, does code collabrator have this function already? I want to get such report but only defect available from the web gui.



Thanks

[#2]

Hi Ajee,



As you already noted, our built-in reports don't support that specific query. 

But you can read that data directly from the database by running a custom SQL query.  Our schema is documented here: 

http://codecollaborator.smartbear.com/docs/manual/7.0/index.html?database_schema.html



The query would roughly look something like: 



SELECT user_guid AS username 

, count(*) AS numComments

FROM user

INNER JOIN comment ON (user_id=comment_userid)

WHERE comment_createdon > $rangeStart 

AND comment_createdon < $rangeEnd

-- The comment table stores some types of hidden data.

-- We're only interested in user-visible comments:

AND comment_type = 'USER'

GROUP BY user_guid;



You can just replace $rangeStart and $rangeEnd to designate a period of time when you want to count comments.  



Notes:



1) If you're going to run this query often, you may want to add an index on the comment_createdon field.  It's not indexed by default because we don't usually search on that field. (An index will make that query faster.)



2) The "user" table may be named "collabuser" if "user" is a reserved key in your database.