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.
