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] <javascript:>>
> 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]<javascript:>
> > 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]<javascript:>
>> > 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] <javascript:>>
>>> 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]<javascript:>
>>> > 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] <javascript:>>
>>>> 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]<javascript:>
>>>> > 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] <javascript:>>
>>>>> 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]<javascript:>
>>>>> > 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]<javascript:>
>>>>>> > 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] <javascript:>> 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]<javascript:>>
>>>>>>>> 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] <javascript:>.
>>>>>>>>
>>>>>>>> To post to this group, send email to
>>>>>>>> [email protected]<javascript:>
>>>>>>>> .
>>>>>>>> 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] <javascript:>.
>>>>>>>> To post to this group, send email to
>>>>>>>> [email protected]<javascript:>
>>>>>>>> .
>>>>>>>> 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] <javascript:>.
>>>>> To post to this group, send email to
>>>>> [email protected]<javascript:>
>>>>> .
>>>>> 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] <javascript:>.
>>>>> To post to this group, send email to
>>>>> [email protected]<javascript:>
>>>>> .
>>>>> 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] <javascript:>.
>>>> To post to this group, send email to
>>>> [email protected]<javascript:>
>>>> .
>>>> 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] <javascript:>.
>>>> To post to this group, send email to
>>>> [email protected]<javascript:>
>>>> .
>>>> 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] <javascript:>.
>>> To post to this group, send email to
>>> [email protected]<javascript:>
>>> .
>>> 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] <javascript:>.
>>> To post to this group, send email to
>>> [email protected]<javascript:>
>>> .
>>> 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] <javascript:>.
> To post to this group, send email to [email protected]<javascript:>
> .
> 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.
