[TYPO3-mvc] order by "calculated attribute" - several approaches

[Jigal van Hemert]

Christian Baer wrote:
> I have two tables, clips and ratings, which are connected via M-M. Clips 
> can be rated with a value from 1 to 5. Now I'd like to have the 
> calculated field "average_rating", which shows the average of all 
> ratings for one clip. But it's only a calculated attribute, nothing is 
> stored. What I need now is something like "order Clips by average_rating".
> 
> I have several approaches:
> 
> 1. fetch all clips and calculate the average_rating in PHP

This would not be very efficient for large amounts of data...

> 2. fetch all clips ordered via a SQL-statement

Ordered by what exactly?

> 3. create an expression in SQL, which calculates the average

That would theoretically be the correct way to retrieve data from a 
database. Unfortunately this is not very efficient either, so...

4. Maintain a field with the average rating (and use it in an index) and 
sort on that field.

De-normalizing data can often be an efficient approach for speed. Maybe 
one day calculated indexes can be created (handy for birthdays, ratings, 
etc.), but until then...

-- 
Jigal van Hemert
skype:jigal.van.hemert
msn: jigal at xs4all.nl
http://twitter.com/jigalvh