CODEWITHSUNDEEP
sphinx
Johno
Johno

Reputation: 1959

Sphinx search for distinct values with counts

I have an "objects" table (actually a result of a SQL join), with data like this:

ID, content, category_id
1, some searchable data, 5
2, some more data, 6
3, more data, 5
4, another example, 7

I'd like to use Sphinx to index this table and return distinct category_id values, as well as how many records had data hits, sorted by the number of hits.

For instance, if I search this index with the term "data", I'd like the result to be:

5, 2 hits
6, 1 hit

This would be pretty trivial with grouping and counts in MySQL, but I can't get my head around doing the same thing with a Sphinx search.

What should my sql_query be? How should I use the PHP API to get the results I need?

Upvotes: 0

Views: 1372

Answers (1)

barryhunter
barryhunter

Reputation: 21091

 $cl->SetGroupBy( "category_id", SPH_GROUPBY_ATTR, "@count desc" );

distinct category-ids would be in total_found

how many records had data hits you don't directly get, the easiest way would be to run a non-group by query, then it is in total_found

Upvotes: 1

Related Questions