1

I have an index with next structure:

+----+-----------+---------+--------------+
| id | entity_id | content | weight_field |
+----+-----------+---------+--------------+
| 1  | 1         | car     | 1.2          |
+----+-----------+---------+--------------+
| 2  | 1         | desert  | 1.45         |
+----+-----------+---------+--------------+
| 3  | 1         | water   | 1.55         |
+----+-----------+---------+--------------+
| 4  | 2         | water   | 1.1          |
+----+-----------+---------+--------------+
| 5  | 2         | desert  | 1.9          |
+----+-----------+---------+--------------+

Could somebody tell me if possible to SUM() field values with grouping? I tried this

SELECT SUM(weight_field) AS sort, entity_id FROM test_index WHERE MATCH ('@content car|desert|water') GROUP BY entity_id ORDER BY sort DESC

But got error:

syntax error, unexpected '(', expecting $end near '()'

I expected to get next result:

+------+-----------+
| sort | entity_id |
+------+-----------+
| 4.2  | 1         |
+------+-----------+
| 3.0  | 2         |
+------+-----------+

The second way good for me: Use custom weight_field (which contain float values as 1.563, 1.02 and etc) to rank results. But I not sure that it possible to do with

OPTION ranker=...
Stafox
  • 1,007
  • 14
  • 25

2 Answers2

1

So, I finnaly find the reason:

In real code, not in example, I named weight_field as weight. So sphinx recognized it as predefined FUNCTION WEIGHT() and throw error which tells that it wants see () after weight.

After fixing it and re-indexing it works.

Stafox
  • 1,007
  • 14
  • 25
0

I believe

SELECT SUM(WEIGHT()) AS sort,...

should work. Where is WEIGHT() is the value as computed by the current ranker.

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • Yes, may be you're right. But is it possible to set my own field `weight_field` as result for WEIGHT()? – Stafox May 16 '16 at 11:32