sphinx GROUP BY

Question

Going to illustrate this so please bear with me.

This is my MYSQL Table structure indexed by sphinx

------- ------- --------------
tag_id | tag   | foreign_id  |
------- ------- --------------
1       love     111
2       blue     222
3      lover     333
4       yellow   444
5       love     555
6       lov      666

What I'm trying to get from sphinx when I query "love" is something like this:

love = 2 hit
lover = 1 hit

Anyone has any idea how that can be done? I tried the following php code but all it output is just a bunch of tag_id for 'love'

$cl->setGroupDistinct('tag');
$cl->setGroupBy('tag', SPH_GROUPBY_ATTR);  
$cl->SetLimits( 0, 10, 500);
$result = $cl->query("love", 'mytags');

I even tried this but it didn't work as well

$cl->setGroupDistinct('tag');

I think sphinx's group by can be used to solve this but I'm pretty clueless. Help is greatly appreciated! Thanks!

Answer 1

I don't think you want to use setGroupDistinct on the same attribute you're grouping by. In SQL terminology that would be similar to:

SELECT COUNT(DISTINCT tag)
FROM my_tags
GROUP BY tag

which as far as I can tell would only return 1 match for each tag.

I think if you just remove the call to setGroupDistinct or change the distinct field to be the foreign id (thus filtering out multiple identical tags attached to one entry) you'll be good to go.