Thinking Sphinx - Sort order

Question

I have a requirement where I want to get results based on the order of IDs that i pass to sphinx:

I did sorting and filtering with ruby and then find order of user_ids to be

user_ids = [1, 3, 2]

Now, I want to apply search on top of this:

User.search_for_ids("Test", {:with => {:page => params[:page], :per_page => 25, :sphinx_internal_id => user_ids}, :sql => {:order => "field(id,#{user_ids.join(',')})"}})

I see that we can pass :sql => :order in options when we need to sort on sql. But that does not give me the result in the expected sorting order.

User.where(:id =>  [1, 2, 3]).order("field(id,#{user_ids.join(',')})")

gives me proper results as expected

Please correct me if i am doing anything wrong here.Is there a way to preserve the order with respect to user_ids ? Any help is much appreciated.

As noted in the comments were this came up, its possible with some nested IF functions, but do you really only have three user_ids or can the list of ids be a LOT longer? — barryhunter
The list of IDs can be really longer. It can be upto 100K IDs.. — Chubby Boy
Hmm, you probably SOL then. I doubt sphinx will let you use 100k nested if statements - rather appropriately would be a stack overflow! I suppose you might have luck with a UDF, but would imagine it would be slow, and very hard to optimize. Is there really no way you can embed this as an attribute? — barryhunter

Zach Kemp Zach Kemp · Accepted Answer · 2013-10-07T07:23:05

I don't believe there is a built-in mechanism for a predetermined sort order, but you can sort after the search results are returned. Obviously this won't work well if you're using any sort of pagination:

user_ids = [1,3,2]
results = User.search_for_ids #...
results.sort_by {|result| user_ids.index(result.id) }

Thinking Sphinx - Sort order

2 Answers