How to Solr index multiple user defined sorting depending on categories?

lets say we have:

3 products, prod1, prod2 and prod3
3 categories, cat1, cat2 and cat3

the product can have custom defined ordering/sorting in each category.

so the unique groups are, product combined with category and category combined order/sort e.g.:

--------------------------
|product  |category|order|
|------------------------|
|prod1    |cat1    |3    |
|prod2    |cat1    |1    |
|prod3    |cat1    |2    |
--------------------------
|prod1    |cat2    |1    |
|prod2    |cat2    |2    |
|prod3    |cat2    |3    |
--------------------------
|prod1    |cat3    |2    |
|prod2    |cat3    |3    |
|prod3    |cat3    |1    |
--------------------------

currently I have each product indexed with all available categories, e.g.:

array (
  'id' => 'prod1',
  'type_s' => 'product',
  'categories_ss' => 
  array (
    0 => 'cat1',
    1 => 'cat2',
    2 => 'cat3',
  ),
  ...
  ...
  ...
  'price_i' => 50
)

I want to be able to search for all products in category x sorted by the custom order. What solr field should I be using to store the orders of the product inside each category?

My Solr version: 4.10.4

Solution

Since you have tagged this question with Solr4, I strongly recommend upgrading - there's many performance enhancements for DocValues (and they were added in 4.2, but I assume that you're at least on a later version than that). Some of them may make this strategy much more viable.

Create a dynamic field with the type int (or long) and with docValues enabled (which should help with memory consumption for sparse fields used for sorting).

<dynamicField name="sort_cat_*" type="int" docValues="true" indexed="true"  stored="true" />

You then add a field to your document that is named after the category or the category id, if possible - avoiding special characters in field names makes everything simpler. Use a simple replacement strategy (i.e. change "foo & bar" into "foo_bar"):

sort_cat_cat1: 2

You can then use this field for sorting. You probably already have a field that you can filter on to get items in this category.