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

[Christian Baer]

Am 07.06.10 10:00, schrieb 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...
>

Hi Jigal,

thanks for the input. I think your De-Normalizing-idea suits quite well 
for my case. Now will add a new field average_rating, and calculate the 
value each time a new rating is added. The only disadvantage is, that I 
have to do this for FE (extbase) and BE (TYPO3-TCA) as well.


Regards,
Chris