[SOLVED] Computed average rating field in CB

Is this possible with a rating field type?

Created a rating field "cb_rate_computed" with:


"

SELECT AVG( `cb_rate_freelancer_general` + cb_rate_freelancer_quality_of_work` + `cb_rate_freelancer_knowledge` + `cb_rate_freelancer_productivity` + `cb_rate_freelancer_independence` + `cb_rate_freelancer_cooperation` + `cb_rate_freelancer_attendance` + cb_rate_freelancer_reliability` + `cb_rate_freelancer_leadership` + `cb_rate_freelancer_appearance` + `cb_rate_freelancer_adherence_to_policy` + `cb_rate_freelancer_initiative` + `cb_rate_freelancer_use_again')/13 FROM `#__comprofiler` WHERE `id` = '[user_id]'

"(minus quotes)

Field is set as Read Only as I only want a get an overal rating based off the other rating fields displayed to end users.

Possible? Would this be better (less site load) to do as an autoaction instead?




Found:
www.joomlapolis.com/forum/153-professional-member-support/231177-computed-field-in-cb#270562

The rating field type calculates the rating based off user ratings submitted to it (uses the _comprofiler_ratings table). You can't replace its calculation with your own. Using a quick simple query is fine; just profile it by enabling debug mode and reviewing the query at the bottom of the page to see its performance. Alternatively you maybe able to update a rating fields value with your query in CB Auto Actions.

What about using CB query to calculation the average, autoactions to run the query on any rating field update, and then substitutions to replace the integer score with stars,bar,graphis, etc? I'm not sure which is the best way to accomplish this, if possible...

A query field with IF substitutions to add stars would probably work using the layouts feature. For example under Parameters > Layouts you'd Profile Value Layout as follows.

[cb:if FIELD_NAME>="1"]STAR_1_IMG[/cb:if]
[cb:if FIELD_NAME>="2"]STAR_2_IMG[/cb:if]
[cb:if FIELD_NAME>="3"]STAR_3_IMG[/cb:if]
[cb:if FIELD_NAME>="4"]STAR_4_IMG[/cb:if]

Replace FIELD_NAME with the name of your cb query field. You don't need CB Auto Actions at all for this usage.

Once again King Kyle nails it. Almost...
Here is what I got so far and the results:

Query:
SELECT ROUND(SUM(cb_rate_freelancer_general+cb_rate_freelancer_use_again+cb_rate_freelancer_attendance+cb_rate_freelancer_cooperation+cb_rate_freelancer_knowledge+cb_rate_freelancer_reliability+cb_rate_freelancer_independence+cb_rate_freelancer_productivity+cb_rate_freelancer_quality_of_work+cb_rate_freelancer_leadership+cb_rate_freelancer_appearance+cb_rate_freelancer_adherence_to_policy+cb_rate_freelancer_initiative)/13) AS cb_rate_computed
FROM `tcr_comprofiler` WHERE `id` = '[user_id]'

This gives me a whole number on the site if I use no substitutions.

If I use:

Layout:
[cb:if cb_rate_computed>="0"]<img src="images/stars/stars0.png" alt="0stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>="1"]<img src="images/stars/stars1.png" alt="1stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>="2"]<img src="images/stars/stars2.png" alt="2stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>="3"]<img src="images/stars/stars3.png" alt="3stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>="4"]<img src="images/stars/stars4.png" alt="4stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>="5"]<img src="images/stars/stars5.png" alt="5stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>="6"]<img src="images/stars/stars6.png" alt="6stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>="7"]<img src="images/stars/stars7.png" alt="7stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>="8"]<img src="images/stars/stars8.png" alt="8stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>="9"]<img src="images/stars/stars9.png" alt="9stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>="10"]<img src="images/stars/stars10.png" alt="10stars" height="50" width="150" />[/cb:if]

I get stars0-9.png all in order on the screen.

If I use:

Layout:
[cb:if cb_rate_computed>=~"0"]<img src="images/stars/stars0.png" alt="0stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"1"]<img src="images/stars/stars1.png" alt="1stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"2"]<img src="images/stars/stars2.png" alt="2stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"3"]<img src="images/stars/stars3.png" alt="3stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"4"]<img src="images/stars/stars4.png" alt="4stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"5"]<img src="images/stars/stars5.png" alt="5stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"6"]<img src="images/stars/stars6.png" alt="6stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"7"]<img src="images/stars/stars7.png" alt="7stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"8"]<img src="images/stars/stars8.png" alt="8stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"9"]<img src="images/stars/stars9.png" alt="9stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"10"]<img src="images/stars/stars10.png" alt="10stars" height="50" width="150" />[/cb:if]

I get nothing.



If I use:

Layout:
[cb:if cb_rate_computed>=~"/0/"]<img src="images/stars/stars0.png" alt="0stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"/1/"]<img src="images/stars/stars1.png" alt="1stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"/2/"]<img src="images/stars/stars2.png" alt="2stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"/3/"]<img src="images/stars/stars3.png" alt="3stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"/4/"]<img src="images/stars/stars4.png" alt="4stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"/5/"]<img src="images/stars/stars5.png" alt="5stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"/6/"]<img src="images/stars/stars6.png" alt="6stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"/7/"]<img src="images/stars/stars7.png" alt="7stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"/8/"]<img src="images/stars/stars8.png" alt="8stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"/9/"]<img src="images/stars/stars9.png" alt="9stars" height="50" width="150" />[/cb:if]
[cb:if cb_rate_computed>=~"/10/"]<img src="images/stars/stars10.png" alt="10stars" height="50" width="150" />[/cb:if]

I get nothing.


I'm lost...

Found it... DOH! After 12 hours you get dumb.. I don't know where the extra > came from since that code was copied from my other substitutions field..oh well. 100!

Thanks Kyle!