[TYPO3-mvc] Repository: sort by sorting of relation from mm table

[Moritz Ahl]

Hi all,

i have categories and products. Products can be associated to one or more categories and vice versa (bi-directional). Products can be sorted within the category, enabling that a product can be on top of category A while being on bottom of category B. This works fine in TCA. 

Now in my list view, I want to list products matching selected categories, ordered according to their sorting in regard to the displayed category:

1. first, order products by the "sorting" field of their category,
2. then, order the products by the "sorting_foreign" column of the relation to their category

Here is my products attribute configuration from TCA:

			'config' => array(
				'type' => 'select',
				'foreign_table' => 'tx_myshop_domain_model_product',
				'MM' => 'tx_myshop_domain_model_product_category_mm',
				'MM_opposite_field' => 'categories',
				'size' => 10,
				'autoSizeMax' => 30,
				'maxitems' => 9999,
				'multiple' => 0,
			),

Now I can of course use a raw SQL statement but this doesn't seem good design to me. Of course I could also just iterate through the categories, and get their products, but that doesnt seem performing.

So is there any way to do this nicely?