0

I'm trying to filter query with HAVING but all I get this error:

mysql> SELECT id FROM related_tags GROUP BY application_id HAVING COUNT(*)=10;
ERROR 1064 (42000): sphinxql: syntax error, unexpected IDENT, expecting $end near 'HAVING COUNT(*)=10'

I'm using Sphinx 2.2.6-id64-release, it supports HAVING

This is my index, if it does matter (application_id attribute is for grouping by id).

sql_query   = \
        SELECT `id`, `id` as `application_id`, `clear_title`\
        FROM `applications`\
        WHERE `id`>=$start AND `id`<=$end

sql_query_range = SELECT MIN(id),MAX(id) FROM applications

sql_attr_uint = application_id
sql_attr_multi = uint tag_id from query; \
    select application_id, tag_id \
    from application_tag_stemmed2;

1 Answers1

0

I think you have to make it a virtual attribute, arbitary expressions not allowed in the HAVING clause itself. 

SELECT id,COUNT(*) AS cnt FROM related_tags GROUP BY application_id HAVING cnt=10;
barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • I've tried this, it even show an error when I'm trying to make virtual value. Only thing that it allows me to make is GROUP BY –  Jan 20 '15 at 07:10
  • mysql> SELECT id, COUNT(*) as count FROM related_tags GROUP BY application_id; ERROR 1064 (42000): sphinxql: syntax error, unexpected COUNT, expecting IDENT near 'count FROM related_tags GROUP BY application_id' –  Jan 20 '15 at 07:11
  • Dont call it count its a reserved word. Or if you must try backticks – barryhunter Jan 20 '15 at 09:29