Thanks Pat! You are right! I see the query being fired as:
SELECT `users`.* FROM `users` WHERE `users`.`id` IN *(26826, 26827)* ORDER BY *field(id,26828, 26827, 26826)*; Which is, it queries in paginated result set and then orders it, that is how SQL SORT works. Is there any other way we have to make SQL SORT work with PAGINATION? or moving the filters to sphinx is the only option we have? Regards, Sabarish S On Tue, Oct 8, 2013 at 9:46 AM, Pat Allan <[email protected]> wrote: > I see… that makes sense, but it just returns us to the original problem, > and the fact that Sphinx doesn't allow such a custom approach to sorting, > short of constructing a complex set of nested IF calls: > http://sphinxsearch.com/docs/manual-2.0.9.html#comparison-functions > > And I'm not even sure if Sphinx handles nested IFs. Perhaps it's worth a > shot, though it's far from elegant. > > > On 08/10/2013, at 3:12 PM, Sabarish Sankar <[email protected]> > wrote: > > Hi Pat, > > I tried the patch. The problem i see is, it is applying sort on top of > paginated results that is, it paginates and then sorts paginated result. > But i want sort to be applied and then paginate the sorted results. > > Thanks, > > > Regards, > Sabarish S > > > On Tue, Oct 8, 2013 at 7:41 AM, Sabarish Sankar < > [email protected]> wrote: > >> Awesome! I will try that out with your fix. Thanks very much for the fix. >> >> >> Regards, >> Sabarish S >> >> >> On Tue, Oct 8, 2013 at 7:37 AM, Pat Allan <[email protected]>wrote: >> >>> Yup, that does sound right :) >>> >>> On 08/10/2013, at 1:05 PM, Sabarish Sankar <[email protected]> >>> wrote: >>> >>> Hi Pat, >>> >>> Thanks very much. >>> >>> *field()* is a MYSQL function that I used here to sort based on given >>> order of IDs >>> >>> >>> http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_field >>> >>> SELECT id from users WHERE id IN (1, 2, 3) ORDER BY field(id,1,3,2); >>> >>> So, I assume if we pass the field() function to :sql => {:order => '* >>> field(id,#{user_ids.join(',')})*'}, that should help me to get the >>> order in the order of IDs provided. Please correct me if i am wrong. >>> >>> Thanks, >>> >>> >>> Regards, >>> Sabarish S >>> >>> >>> On Tue, Oct 8, 2013 at 5:00 AM, Pat Allan <[email protected]>wrote: >>> >>>> Hi Sabarish >>>> >>>> I'm afraid Sphinx doesn't have anything like the field() function - so >>>> there's no way of having that sorting work dynamically within Sphinx. If >>>> the order is quite specific and can be stored in the database, then you >>>> could use that value as an attribute for Sphinx ordering… but I'm guessing >>>> that's not the case. >>>> >>>> As for why :sql => {:order … } isn't working, it turns out there's a >>>> bug… which I've just fixed. To use the latest version, put this in your >>>> Gemfile: >>>> >>>> gem 'thinking-sphinx', '~> 3.0.5', >>>> :git => 'git://github.com/pat/thinking-sphinx.git', >>>> :branch => 'master', >>>> :ref => '5dd6f4bfdd' >>>> >>>> -- >>>> Pat >>>> >>>> On 08/10/2013, at 5:09 AM, Sabarish Sankar <[email protected]> >>>> wrote: >>>> >>>> > A small update, >>>> > >>>> > user_ids = [1, 3, 2] >>>> > >>>> > I tried with :sql => {:order => "field(id,#{user_ids.join(',')})"} >>>> > >>>> > but that still does not give me the result in the expected sorting >>>> order.I see that we can pass :sql => :order in options when we need to sort >>>> on sql. >>>> > >>>> > 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. >>>> > >>>> > Thanks, >>>> > >>>> > >>>> > >>>> > >>>> > Regards, >>>> > Sabarish S >>>> > >>>> > >>>> > On Mon, Oct 7, 2013 at 8:02 PM, JSWorld <[email protected]> >>>> wrote: >>>> > Hi Pat, >>>> > >>>> > 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 like : >>>> > >>>> > User.search_for_ids("Test", {:with => {:page => params[:page], >>>> :per_page => 25, :sphinx_internal_id => user_ids}, :order => user_ids}) >>>> > >>>> > Is there a way to preserve the order with respect to user_ids ? Any >>>> help is much appreciated. >>>> > >>>> > Thanks, >>>> > >>>> > >>>> > -- >>>> > You received this message because you are subscribed to the Google >>>> Groups "Thinking Sphinx" group. >>>> > To unsubscribe from this group and stop receiving emails from it, >>>> send an email to [email protected]. >>>> > To post to this group, send email to [email protected] >>>> . >>>> > Visit this group at http://groups.google.com/group/thinking-sphinx. >>>> > For more options, visit https://groups.google.com/groups/opt_out. >>>> > >>>> > >>>> > -- >>>> > You received this message because you are subscribed to the Google >>>> Groups "Thinking Sphinx" group. >>>> > To unsubscribe from this group and stop receiving emails from it, >>>> send an email to [email protected]. >>>> > To post to this group, send email to [email protected] >>>> . >>>> > Visit this group at http://groups.google.com/group/thinking-sphinx. >>>> > For more options, visit https://groups.google.com/groups/opt_out. >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "Thinking Sphinx" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To post to this group, send email to [email protected]. >>>> Visit this group at http://groups.google.com/group/thinking-sphinx. >>>> For more options, visit https://groups.google.com/groups/opt_out. >>>> >>> >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Thinking Sphinx" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at http://groups.google.com/group/thinking-sphinx. >>> For more options, visit https://groups.google.com/groups/opt_out. >>> >>> >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Thinking Sphinx" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at http://groups.google.com/group/thinking-sphinx. >>> For more options, visit https://groups.google.com/groups/opt_out. >>> >> >> > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/thinking-sphinx. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/thinking-sphinx. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/thinking-sphinx. For more options, visit https://groups.google.com/groups/opt_out.
