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.
