[TYPO3-mvc] Sort query results by a sub-model property

[Franz Koch]

Hey folks,

>> --------------------------------------------------
>> SELECT DISTINCT tx_hypeshowcase_domain_model_project.*
>> FROM   tx_hypeshowcase_domain_model_project
>>          LEFT JOIN tx_hypeshowcase_domain_model_product
>>            ON tx_hypeshowcase_domain_model_project.uid =
>>               tx_hypeshowcase_domain_model_product.project
>> WHERE  tx_hypeshowcase_domain_model_product.uid IN (SELECT uid_local
>>                                                       FROM
>>                 tx_hypeshowcase_relation_product_service
>>                                                       WHERE  uid_foreign = 9)
>> ORDER  BY tx_hypeshowcase_domain_model_product.date DESC
>> --------------------------------------------------
>>
>> Probably "ORDER  BY tx_hypeshowcase_domain_model_product.date DESC"
>> is wrong but I'm not sure, and I dunno how to set it right.
>
> I think extbase does exacly, what it has been told to to do.
> I think it's rather more an problem of the design than of extbase or
> mysql, because you cannot order by the relation of an joined table.
> Without the "DISTINCT" you will get your project results times the
> relations it has to the joined tables in order of the field.
> With "DISTINCT" you will get only the first row of every project, as you
> mentioned before, which is correct for the query and the repositiory code.
> So you could try to find the uids of the products with the max date
> (core db api and group by tx_hypeshowcase_domain_model_product.project)
> and add an additional contraint with those uids to the query object.
> Not nice, but should work. Another option would be to handle the
> ordering by an service class after fetching all projects.

I see it like Marc Sebastian, there is no easy way to order by a field 
in a 1:n relation. You would have to use a subselect to determine the 
newest product first and then use this product for the joins. Not sure 
if it's allowed to use subselects for a "ON" statement in joins. Xavier? 
Or some other DB guru?

-- 
kind regards,
Franz Koch