How to implement custom sort order with some items at fixed places in solr?

Question

I have a list of drawers in a mysql table named Drawers like

|      BOX       |  RANK  |
|----------------|--------|
|     Box1       |   1    |
|     Box2       |   2    |
|     Box3       |   3    |
|     Box4       |   4    |
|     Box5       |   5    |

Then I have another source which says some of these boxes contains jewel and should be placed at a specific position only(Lets call this table jewelboxes).

|      BOX       |  RANK  |
|----------------|--------|
|     Box1       |   4    |
|     Box3       |   1    |
|     Box5       |   3    |

I have certain restrictions that needs to adhere to:

I cannot write a stored proc on these tables

I want to get a list of Boxes on Solr where position of the jewelboxes should be fixed irrespective of the calling order(ascending/descending). for example,

ascending order would be:

|      BOX       |  RANK  |
|----------------|--------|
|     Box3       |   1    |
|     Box2       |   2    |
|     Box5       |   3    |
|     Box1       |   4    |
|     Box4       |   5    |

descending order would be:

|      BOX       |  RANK  |
|----------------|--------|
|     Box3       |   1    |
|     Box4       |   2    |
|     Box5       |   3    |
|     Box1       |   4    |
|     Box2       |   5    |

I am importing these tables into solr from dih, and currently ripping my hair apart thinking about how to do this. I have 2 options in my mind, but both are not very clear, and would like you folks here to help me out. My options are:

1. Write a query in such a way that'll give me correct order. (this would need a master level querying skills because all we have is a select query in dih)
2. Write a CustomFieldComparator as described in the following link: http://sujitpal.blogspot.in/2011/05/custom-sorting-in-solr-using-external.html

Is there any third approach which can be followed to get the desired results ?

UPDATE:

I can work without the descending order criteria, but I still need the ascending one.

Thanks :-)

Answer 1

I would create a custom indexer in a language you are comfortable with (see https://wiki.apache.org/solr/IntegratingSolr - I work in Python and use mysolr) when you need more flexibility like this. Maybe create two fields, one for "ascending" and one for "descending"; make a list with the ranks, one with reverse ranks, then query the second source to use for overrides on both lists - or whatever you need to do to process the data. I guess the feasibility also depends on how many records you need to index. Then process each record, adding the information for the two ranking fields from the list, and send them to your Solr server in batches.