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

[Christian Baer]

Am 07.06.10 13:29, schrieb Dmitri Pisarev:
> On 07.06.2010 11:40, Christian Baer wrote:
>> Hey,
>>
>> I'm not quite sure where to put the logic for a calculated attribute.
>>
>> 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
>> 2. fetch all clips ordered via a SQL-statement
>> 3. create an expression in SQL, which calculates the average
>>
>> With the first two approaches I cannot use the normal
>> Extbase-Repository-Querys, which is quite bad. The third one puts the
>> logic into SQL, which I don't really prefer.
>>
>> Now, is there a Extbase-way to handle this? Is there a way to add an
>> attribute to the Clip-Model, which calculates the average and then could
>> be used in $query->setOrderings(...)? Maybe adjust something in the
>> Mapping-process?
>>
>>
>>
>> Regards,
>> Chris
>>
> Hi Chris!
> Maybe you can implement the sorting in getter or in the viewhelper?
>
> Dmitri.

Hi Dimitri,

that's what I tried in first place. I implemented a ViewHelper that 
calculates the average rating to show in FE. But this still left me with 
the problem of the orderBy. As I wrote I need the logic of 
rating-average at the point where I am fetching data from the database, 
so to say "order by average_rating".

Now I follow the hint of Jigal and de-normalize my data.


Regards
Chris