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