1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. Putting in a bit of competition into CB activity

Putting in a bit of competition into CB activity

5 years 3 months ago #310042 by timstohr
Putting in a bit of competition into CB activity was created by timstohr
Hi Kyle,
I have a quora-like site for expatriates. I wanted to put in a bit of competition in order to increase user answers. For that I wanted to have a couple of fields in the profile showing how many comments to a certain activity (identifiable by its asset "mainforum") they have made. That should show on their profile (with an associated rank, beginner, knows something, expert, answer master).
Most of all though, I would like to have a module in order to show the person with the highest number of comments or the person with the highest number of activity (again, if possible, only those with a certain asset).

Any possibility to do that?

Please Log in to join the conversation.

5 years 3 months ago #310066 by krileon
Replied by krileon on topic Putting in a bit of competition into CB activity
Easiest way is probably a Query field using CB Query Field then just query for a count of those comments. Example as follows.

SELECT COUNT(*)
FROM `#__comprofiler_plugin_activity_comments` AS c
WHERE c.`user_id` = USER_ID_HERE
AND c.`asset` IN ( SELECT CONCAT( 'activity.', a.`id` ) FROM `#__comprofiler_plugin_activity` AS a WHERE a.`asset` = 'ASSET_HERE' )

Ideally this would just have an inner join of the activity to filter things down, but without the object and target columns (not implemented yet) the query is a little odd, but it works if you want to use that method instead.

SELECT COUNT(*)
FROM `#__comprofiler_plugin_activity_comments` AS c
INNER JOIN `#__comprofiler_plugin_activity` AS a
ON a.`id` =  SUBSTRING( c.`asset`, 10 )
AND a.`asset` = 'ASSET_HERE'
WHERE c.`user_id` = USER_ID_HERE
AND c.`asset` LIKE 'activity.%'

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

5 years 2 months ago #310286 by timstohr
Replied by timstohr on topic Putting in a bit of competition into CB activity
Hi just got round to testing it out and having some issues.

I think the queries are not right. I am always getting 0 out of either one.

The problem I think is that I have no clue what I should put in USER_ID_HERE (ie what substitution) as I need to have the persons ID on whose field the Query field is on, not the viewers user ID (for that one I can use [user_id])
The other problem is that I am using the asset "mainforum.[user_id]". Here again I would need the ID of the profile that the query field is on.

Please Log in to join the conversation.

5 years 2 months ago #310305 by krileon
Replied by krileon on topic Putting in a bit of competition into CB activity
The queries work fine in my tests. You need to of course replace ASSET_HERE and USER_ID_HERE. Treat all substitutions as strings so replace USER_ID_HERE with '[user_id]' for example. Substitutions in Query and Code fields are always based off the profile owner data.

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. Putting in a bit of competition into CB activity
Time to create page: 0.197 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Professional Members Support
  5. Putting in a bit of competition into CB activity

Search

Login / Logout

User Menu