Hi Sabarish What might work is having an index on your profile field model instead, and then you can sort by contact number there quite simply (and refer to the associated user for additional fields/attributes if necessary).
Does that sound like it might work? -- Pat On 17/10/2013, at 11:27 PM, Sabarish Sankar <[email protected]> wrote: > Hi Pat, > > My backend architecture is that, > > Group has many profile fields - Ex: contact number is one sample profile > field (profile field is dynamic - Administrator can add/remove fields) > > User has many profile answers (User will have an answer to every profile > field of group) > > My requirement is, > > I want to sort the users based on their answers for any specific question > (Ex: I can sort based on contact number field) > > What is the best way to create index for such scenarios so that i can use > sphinx to sort? Thanks again for your help. > > > > > > > Regards, > Sabarish S > > > On Tue, Oct 8, 2013 at 11:53 AM, Pat Allan <[email protected]> wrote: > The SQL query occurs to translate Sphinx results into ActiveRecord objects - > so, it's not something that can occur before the Sphinx query. If you can't > push the sorting logic into the Sphinx query, then your original approach > (really large max_matches/per_page values) is perhaps the best. > > An alternative is to rework your app so this sorting isn't required, but > perhaps that's not an option :) > > -- > Pat > > > On 08/10/2013, at 5:19 PM, Sabarish Sankar <[email protected]> > wrote: > >> 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. > > > -- > 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.
