does someone knows why this first query works and this second not?
first query (works):
SELECT *,
MATCH(username) AGAINST ('ergergergergergerg' IN BOOLEAN MODE) AS _score
FROM (ci_users) HAVING _score > '0';
second query (not working):
SELECT COUNT(id),
MATCH(username) AGAINST ('ergergergergergerg' IN BOOLEAN MODE) AS _score
FROM (ci_users) HAVING _score > '0';
the first query returns exactly 24 rows, the second query returns 0 rows, while i just changed *
to COUNT(id)
The first one return every row that matches.
The second one contains an aggregation on id.
When aggregating, MySQL is smart enough (or should I say, stupid enough) to implicitly pick a any value from all fields that are queried, but not aggregated. So since you don't group by score
and you don't aggregate score
, MySQL will just return any of the scores of all records, of the intermediate result (before applying the having
clause) including those that have a value of 0. So, this intermediate result may very well contain just a count of, say, 900 and a _score of 0. After filtering using having
, no records remain.
Now, I would be happy to do a suggestion, but I cannot tell what you would actually want to be returned from that query.
SELECT COUNT(id)
FROM
(SELECT
id,
MATCH(username) AGAINST ('ergergergergergerg' IN BOOLEAN MODE) AS _score
FROM (ci_users) HAVING _score > '0') x;
Or even better:
SELECT COUNT(id)
FROM (ci_users)
WHERE MATCH(username) AGAINST ('ergergergergergerg' IN BOOLEAN MODE) > '0'