Thinking sphinx ancestry searching

Question

I need to get all of the users from groups including subgroups:

app/indices/user_index.rb

 ThinkingSphinx::Index.define :user, with: :active_record, delta: ThinkingSphinx::Deltas::SidekiqDelta do
   has groups.id
   has "CONCAT_WS('/',groups.id,groups.ancestry)", as: :group_ids, type: :integer, multi: true
end

But when i'm trying to search:

User.search_for_ids(with_all: { group_ids: [3] })

It returns all of the users from subgroups, but without users from group with id 3

Sphinx 2.1.7 running undex arch linux
Thinking sphinx v3.1.1

Is there a reason why you're using / as a separator instead of a comma? — pat
ancestry uses / as separator by default. Using different gives no result. — user3332726
Sphinx expects commas as integer separators in multi-value attributes. Can you change all /s to , within that generated string? And also, you should be able to check the generated value by running the generated sql_query from your user_core_0 source in config/development.sphinx.conf, with an altered WHERE clause for a specific user. — pat

user3332726 user3332726 · Accepted Answer · 2015-02-12T09:08:30

Finally found out the problem:

has "CONCAT_WS('/',groups.id,groups.ancestry)", as: :group_ids, type: :integer, multi: true

was returning only 1 id or ancestry per group, meaning if user has few root groups, e.g 3,5 the expression above will return only 1 group:

+----+--------+-----------+
| id | groups | group_ids |
+----+--------+-----------+
| 39 | 5      | 5/3       |
| 40 | 245,3  | 245       |
| 42 | 5      | 5/3       |
| 43 | 234    | 234/3/5   |

and the user with id 40 was was not finding. But, if you notice, everything works fine for groups column. The solution is to use group concat:

has "CONCAT_WS('/',GROUP_CONCAT(DISTINCT groups.`id` SEPARATOR '/'), GROUP_CONCAT(groups.`ancestry` SEPARATOR '/') )", as: :group_ids, type: :integer, multi: true

Result:

+----+--------+-----------+
| id | groups | group_ids |
+----+--------+-----------+
| 39 | 5      | 5/3       |
| 40 | 245,3  | 245/3     |
| 42 | 5      | 5/3       |
| 43 | 234    | 234/3/5   |

Also, it works fine with "/" separator.

Thinking sphinx ancestry searching

1 Answers