Average Rating: How To

[aijosh]

Hello,

I need to display the average rating of all the rating records of a user from some list into another list.

So
:: a user can add say 20 record into different lists (or the same list)
:: each record of the user has a rating

I need to read all the ratings of the user and do an average (this would not be the rating of the user) and display this result on another list or/and detail view where the user profile is loaded. [this list does not have a rating element]

I dont know which route is best. I'm thinking should I do a php to read the database and all tables each time the list and detail view is loaded or try a calc?..... not sure and need direction.

Note that a lot of user profiles (showing their average rating) would be displayed at once, so speed is a big factor here

Also since this rating is like a real time thing dependent on all other ratings and changes each time, is it necessary to save the record in the database or its best to just always do a realtime calculation?

 

[aijosh]

Anybody with advice on this?

 

[cheesegrits]

Sorry, thought I'd replied to this.

If you weren't loading a bunch of rows and speed wasn't critical, I'd say a calc. But ... as you need quantity and speed ...

The way I've done things like this in the past is to use a form PHP plugin, running 'onBeforeLoad', and a list php_events plugin, running 'onPreLoadData', on the target table (the one that has the averages that need updating), both of which do the same thing, and basically just run a query to update the target table. I usually just update the whole thing, in list or form, even though technically in form view you only need to update for the user the row is for, but that involves finding out the userid, which is another query (as on onBeforeLoad you only have the rowid, not the data).

Here's an example of updating one table with averages from another based on joining by (in this case) "movie id":

https://stackoverflow.com/questions/23532206/update-with-an-avg-from-another-table

So your two plugins would just run that, tweaked for your table and field names.

Obviously run a test query by hand first, and make sure performance is acceptable. But as long as there's an index on your user_id field in the target table (which there might not be if you haven't added an element filter to it, or use it for grouping, etc - basically anything that would make Fabrik create an index ... so check, and create on by hand if necessary) and are of the right type (so INT or DECIMAL for the value ones, not VARCHAR or TEXT), even for fairly large tables it should be quick.

The only other way, if you are happy getting down into MySQL, is to create a trigger function on table1, which updates table 2 whenever table 1 is updated, with a similar query (although you could add a WHERE clause to restrict it to just the user ID involved).

-- hugh