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.
