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.
