Yup, annoyingly Sphinx doesn't return string attributes properly if they're the group value, so you'll need to use the pane to get the proper attribute value instead (or just list_item.user_item.user.username - though that's not ideal from a database perspective).
On 17 Mar 2014, at 9:52 pm, J. Garcia <[email protected]> wrote: > Pat, > Got back to this. > > I have been trying your update of alias sphinx_internal_count > I have been changing my indexing and query a bit. > Now, what I index is: > > ThinkingSphinx::Index.define :list_item, :with => :active_record do > has user_item.artifact_id > has user_item.user_id > has user_item.user.username > has list.type > has state > end > > If I do: > search = ListItem.search :with => {:artifact_id => 47}, group_by: > "user_id", order_by: 'sphinx_internal_count'; > groups = [] > search.each_with_group_and_count { |i,g,c| groups << [g, c] } > groups > > I get the result ordered by count as wished, with the group_by user_id: > [[6, 14], [7, 9]] > > If I do the same with username (a string) instead of user_id: > search = ListItem.search :with => {:artifact_id => 47}, group_by: > "username", order_by: 'sphinx_internal_count'; > groups = [] > search.each_with_group_and_count { |i,g,c| groups << [g, c] } > groups > > I get the result ordered by count as wished, with a calculated integer field > (a username crc?): > [[-1448043994804220252, 14], [-1050173307187172618, 9]] > > Then, in order to get the username, I need to use the panes, right? > > > > > > > On Tue, Mar 4, 2014 at 3:28 PM, Pat Allan <[email protected]> wrote: > I've just pushed a patch to the develop branch to add aliases for both group > and count: sphinx_internal_group and sphinx_internal_count. Such a smart and > obvious idea - thanks for the suggestion :) > > That doesn't explain the nil records you're seeing, though... just to > confirm, what's the search query you're making to get those search results? > > On 4 Mar 2014, at 4:39 am, J. Garcia <[email protected]> wrote: > >> That worked fine, almost. What about using internally __count or similar as >> alias? >> >> I explain the "almost": >> At present I only have 4 ListItem indexed, and when grouped there should >> only be two of them. >> But if I use >> >> groups = [] >> search.each_with_group_and_count do |i,g,c| >> groups << [g,c] >> end >> >> I get: >> >> [[1, 6], [2, 4], [nil, nil], [nil, nil]] >> >> And I can see the query launched against mysql to recover 2 ListItems: >> ListItem Load (0.5ms) SELECT `list_items`.* FROM `list_items` WHERE >> `list_items`.`id` IN (17, 1) >> >> The more verbose alternative implementation works fine: >> search = ListItem.search with: {artifact_id: 47}, group_by: 'user_id', >> select: '*, count(*) as total', order: 'total DESC' >> search.context[:panes] << ThinkingSphinx::Panes::AttributesPane >> res = [] >> search.each do |item| >> res << [item.sphinx_attributes['user_id'], >> item.sphinx_attributes['total']] >> end >> res >> >> [[1, 6], [2, 4]] >> >> El lunes, 3 de marzo de 2014 14:15:37 UTC+1, Pat Allan escribió: >> The following should work around this issue: >> >> ListItem.search :with => {:artifact_id => 85}, :group_by => 'user_id', >> :select => '*, count(*) as total', :order => 'total DESC' >> >> It's annoying, but I'm currently loath to alias count(*) by default when >> grouping by attributes, as the alias may end up being the name of an >> existing attribute. >> >> On 3 Mar 2014, at 5:34 am, J. Garcia <[email protected]> wrote: >> >>> Hey Pat, >>> I was playing a bit with group_by and order and I haven't been able to make >>> it work smoothly, so that group_by results are ordered by count(*): >>> >>> search = ListItem.search :with => {:artifact_id => 85}, group_by: >>> "user_id", :order => "count(*) desc" >>> >>> ThinkingSphinx::SphinxError: index list_item_core: sort-by attribute >>> 'count(' not found >>> from >>> .../gems/thinking-sphinx-3.1.0/lib/thinking_sphinx/connection.rb:90:in >>> `rescue in query' >>> from >>> .../gems/thinking-sphinx-3.1.0/lib/thinking_sphinx/connection.rb:93:in >>> `query' >>> >>> The raw SQL query that is submitted to Sphinx is: >>> >>> "SELECT *, groupby(), count(*) FROM `list_item_core` WHERE `artifact_id` = >>> 85 AND `sphinx_deleted` = 0 GROUP BY `user_id` ORDER BY count(*) desc LIMIT >>> 0, 20" >>> >>> >>> If I launch this query under the sql console, I get this: >>> ERROR 1064 (42000): index list_item_core: sort-by attribute 'count(' not >>> found >>> >>> However, if I modify the raw query to something like this, it works well: >>> SELECT *, groupby(), count(*) as total FROM `list_item_core` WHERE >>> `artifact_id` = 85 AND `sphinx_deleted` = 0 GROUP BY `user_id` ORDER BY >>> total desc LIMIT 0, 20; >>> >>> If I try the option order_group_by, I get the same error. >>> >>> Am I doing something wrong? >>> >>> >>> >>> On Fri, Feb 28, 2014 at 10:46 AM, J. Garcia <[email protected]> wrote: >>> Just this will do, to keep things simpler and similar to order_by option: >>> ListItem.search :with => {:artifact_id => 85}, :group_by => "user_id, >>> type"; '' >>> >>> Works great! >>> >>> I'll submit a patch for Riddle. >>> Thanks. >>> >>> >>> >>> >>> On Fri, Feb 28, 2014 at 4:32 AM, Pat Allan <[email protected]> >>> wrote: >>> Okay, let's try the following: >>> >>> search = ListItem.search :with => {:artifact_id => 85}, :group_by => >>> [:user_id, :type]; '' >>> search.context[:panes] << ThinkingSphinx::Panes::AttributesPane >>> search.each do |list_item| >>> list_item.sphinx_attributes['count(*)'] # count >>> list_item.sphinx_attributes['user_id'] >>> list_item.sphinx_attributes['type'] >>> end >>> >>> The ; '' at the end of the first line is only required if you're running >>> this in the console, as you don't want the search to have inspect called >>> (that will populate results, and you need to add the AttributesPane >>> *before* populating). >>> >>> -- >>> Pat >>> >>> On 27 Feb 2014, at 11:32 pm, J. Garcia <[email protected]> wrote: >>> >>>> ThinkingSphinx::Index.define :list_item, :with => :active_record do >>>> has user_item.artifact_id >>>> has user_item.artifact.name >>>> has user_item.user_id >>>> has list.type >>>> has state >>>> end >>>> >>>> For a given artifact_id, I need to count per user_id and type, how many >>>> list_items are. >>>> >>>> a = ListItem.search :with => {:artifact_id => 85}, group_by: [user_id, >>>> type] >>>> >>>> a.each_with_group_and_count do |i, g, c| >>>> # g could be an array for group_by multiple fields, or a single element >>>> for group_by one field >>>> end >>>> >>>> >>>> >>>> >>>> On Thu, Feb 27, 2014 at 12:27 PM, Pat Allan <[email protected]> >>>> wrote: >>>> Can you provide a full example of the index definition and the search >>>> call? I'm thinking what you're after should be possible, just easier to be >>>> certain with a bit more context :) >>>> >>>> On 27 Feb 2014, at 9:15 pm, J. Garcia <[email protected]> wrote: >>>> >>>>> Trouble with each is I just get the rehydrated ActiveRecord objects, and >>>>> no info on the grouping. With each_with_count, same plus the count. I >>>>> would need the grouped field values as well, as returned from Sphinx raw >>>>> query. I'm not sure how to get them or how to patch Riddle in order to >>>>> obtain them. Any hint would be appreciated. >>>>> >>>>> >>>>> On Thu, Feb 27, 2014 at 12:05 AM, Pat Allan <[email protected]> >>>>> wrote: >>>>> Yeah, I don't think you're going to be able to use >>>>> `each_with_group_and_count` when grouping by multiple columns - you'll >>>>> just have to use `each_with_count` or just `each`, and refer to those >>>>> columns specifically. >>>>> >>>>> Appreciate the suggestion for the patch to Riddle - if you want to submit >>>>> a pull request (on the develop branch) that'd be brilliant, but otherwise >>>>> I'll sort it out soon. >>>>> >>>>> On 27 Feb 2014, at 5:36 am, J. Garcia <[email protected]> wrote: >>>>> >>>>>> Pat, >>>>>> I do not know if it has further implications for older Sphinx versions, >>>>>> but if I patch the code, I still cannot get the group counts. >>>>>> >>>>>> When issuing the raw SQL command for two group fields, Sphinx will >>>>>> return a specific column for each grouped field, plus a groupby() column >>>>>> with what seems like a CRC. I still cannot get this info via >>>>>> each_with_group_and_count, as it returns the groupby() column with some >>>>>> CRC value. >>>>>> >>>>>> >>>>>> >>>>>> On Wed, Feb 26, 2014 at 2:45 PM, J. Garcia <[email protected]> wrote: >>>>>> I've been debugging the code and found the culprit: >>>>>> >>>>>> class Riddle::Query::Select >>>>>> ... >>>>>> def to_sql >>>>>> sql = "SELECT #{ extended_values } FROM #{ @indices.join(', ') }" >>>>>> sql << " WHERE #{ combined_wheres }" if wheres? >>>>>> sql << " #{group_prefix} #{escape_column(@group_by)}" if >>>>>> !@group_by.nil? >>>>>> unless @order_within_group_by.nil? >>>>>> sql << " WITHIN GROUP ORDER BY >>>>>> #{escape_columns(@order_within_group_by)}" >>>>>> end >>>>>> sql << " HAVING #{@having.join(' AND ')}" unless @having.empty? >>>>>> sql << " ORDER BY #{escape_columns(@order_by)}" if !@order_by.nil? >>>>>> sql << " #{limit_clause}" unless @limit.nil? && @offset.nil? >>>>>> sql << " #{options_clause}" unless @options.empty? >>>>>> >>>>>> sql >>>>>> end >>>>>> >>>>>> It calls escape_column for @group_by, instead of escape_columns as for >>>>>> @order_within_group_by. >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Wed, Feb 26, 2014 at 1:36 PM, J. Garcia <[email protected]> wrote: >>>>>> Pat, >>>>>> Thanks for your response. >>>>>> Just passing any of the two fields in a string worked fine. When I tried >>>>>> to pass the two in a comma-separated string I got this stacktrace: >>>>>> >>>>>> >>>>>> ThinkingSphinx::SyntaxError: sphinxql: syntax error, unexpected >>>>>> $undefined, expecting IDENT (or 5 other tokens) near '`user_id,` type >>>>>> LIMIT 0, 20; SHOW META' >>>>>> from >>>>>> .../gems/thinking-sphinx-3.1.0/lib/thinking_sphinx/connection.rb:90:in >>>>>> `rescue in query' >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Tue, Feb 25, 2014 at 11:59 PM, Pat Allan <[email protected]> >>>>>> wrote: >>>>>> Can you try passing in a string of comma-separated attributes, instead >>>>>> of an array of symbols? >>>>>> >>>>>> -- >>>>>> Pat >>>>>> >>>>>> On 26 Feb 2014, at 4:47 am, J. Garcia <[email protected]> wrote: >>>>>> >>>>>>> Hi, >>>>>>> According to Sphinx, it is possible to do a group_by on multiple fields >>>>>>> since version 2.1.2: http://sphinxsearch.com/bugs/view.php?id=1453 >>>>>>> >>>>>>> I haven't found a way to do a search on multiple fields with Thinking >>>>>>> Sphinx. >>>>>>> Is it possible to group by multiple fields on Thinking Sphinx? >>>>>>> I have tried by including several group_by but does not seem to work. >>>>>>> And passing an array of attributes does not seem to be accepted by >>>>>>> Thinking Sphinx: >>>>>>> >>>>>>> ThinkingSphinx::SyntaxError: sphinxql: syntax error, unexpected >>>>>>> $undefined, expecting IDENT (or 5 other tokens) near '`[:user_id,` >>>>>>> :type] LIMIT 0, 20; SHOW META' >>>>>>> from >>>>>>> .../gems/thinking-sphinx-3.1.0/lib/thinking_sphinx/connection.rb:90:in >>>>>>> `rescue in query' >>>>>>> >>>>>>> J. >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> 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 a topic in the >>>>>> Google Groups "Thinking Sphinx" group. >>>>>> To unsubscribe from this topic, visit >>>>>> https://groups.google.com/d/topic/thinking-sphinx/bZpFqr1-iTU/unsubscribe. >>>>>> To unsubscribe from this group and all its topics, 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 a topic in the >>>>> Google Groups "Thinking Sphinx" group. >>>>> To unsubscribe from this topic, visit >>>>> https://groups.google.com/d/topic/thinking-sphinx/bZpFqr1-iTU/unsubscribe. >>>>> To unsubscribe from this group and all its topics, 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 a topic in the >>>> Google Groups "Thinking Sphinx" group. >>>> To unsubscribe from this topic, visit >>>> https://groups.google.com/d/topic/thinking-sphinx/bZpFqr1-iTU/unsubscribe. >>>> To unsubscribe from this group and all its topics, 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 a topic in the >>> Google Groups "Thinking Sphinx" group. >>> To unsubscribe from this topic, visit >>> https://groups.google.com/d/topic/thinking-sphinx/bZpFqr1-iTU/unsubscribe. >>> To unsubscribe from this group and all its topics, 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 a topic in the Google > Groups "Thinking Sphinx" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/thinking-sphinx/bZpFqr1-iTU/unsubscribe. > To unsubscribe from this group and all its topics, 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/d/optout. -- 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/d/optout.
