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

[Marc Bastian Heinrichs]

Hey Thomas,

> The resulting query is this one:
> 
> --------------------------------------------------
> 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 think this is an use case that could be handled by extbase, but
we have to look for an nice solution for this first.

Any other ideas from the database or Typo3DbBackend storage class
experts? :-)

Best
Marc Bastian