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 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.
