How do I order rows by number of fields matched when GROUP_CONCAT is used?

Question

I am trying to order my search results based on the number of columns that are matched.

The variables :Name, :Email, and :Fullnumbers get % before and after in PHP.

The query works fine when I leave out the ORDER BY clause. It also works when I remove Fullnumbers from the ORDER BY clause. The rows where both Name and Email are partially matched are returned in the right order. However, when Fullnumbers is included no rows are returned.

Why is this and how can I solve the problem?

SELECT SQL_CALC_FOUND_ROWS DISTINCT(User.UserId), Name, Email,
GROUP_CONCAT(CONCAT(Countrycode, Number)) AS Fullnumbers
FROM User LEFT JOIN UserPhonenumber ON User.UserId = UserPhonenumber.UserId
GROUP BY UserId HAVING Name LIKE :Name OR Email LIKE :Email OR Fullnumbers LIKE :Fullnumbers
ORDER BY ((Name LIKE :Name) + (Email LIKE :Email) + (Fullnumbers LIKE :Fullnumbers)) DESC

Welcome to SO! I haven't ever seen such an `ORDER BY` clause. What are you trying to obtain? Can you provide some example rows and result desired? — Reversal, Mar 27 '16 at 16:00
Thanks! I want to order by relevance. Boolean values are treated like ints so if there is a match on the email and not on the others it would be 0 + 1+ 0 — Kilgore, Mar 27 '16 at 16:03
ok, just saw your edit to the comment: i'm going to edit my answer. — Reversal, Mar 27 '16 at 16:20

score 0 · Accepted Answer · edited May 23 '17 at 10:28

0

You want to use an ORDER BY CASE clause then. Here is an example:

ORDER BY CASE
  WHEN Name LIKE :Name THEN 1
  WHEN Email LIKE :Email THEN 2
  WHEN Fullnumbers LIKE :Fullnumbers THEN 3
  ELSE 4 END;

EDIT

Using several IF clauses you can order your result set by relevance.

ORDER BY
  IF (Name LIKE :Name,1,0) + 
  IF (Email LIKE :Email,1,0) +
  IF (Fullnumbers LIKE :Fullnumbers,1,0) 
DESC;

Reference: MySQL order by relevance

EDIT 2 Solution:

SELECT * FROM (
  SELECT SQL_CALC_FOUND_ROWS DISTINCT(User.UserId), Name, Email,
  GROUP_CONCAT(CONCAT(Countrycode, Number)) AS Fullnumbers
  FROM User LEFT JOIN UserPhonenumber ON User.UserId = UserPhonenumber.UserId
  GROUP BY UserId HAVING Name LIKE :Name OR Email LIKE :Email OR Fullnumbers LIKE :Fullnumbers) AS t1
ORDER BY
  IF (Name LIKE :Name,1,0) + IF (Email LIKE :Email,1,0) + IF (Fullnumbers LIKE :Fullnumbers,1,0) DESC;

edited May 23 '17 at 10:28

Community

1
1

answered Mar 27 '16 at 16:09

Reversal

622
5
19

Thanks a lot for the answer. I haven't tested it yet as it is late now and I've just returned from a party, but if it works I would be grateful if you could explain why your ORDER BY clause works and mine doesn't. I'll of course accept your answer if it works as expected. – Kilgore Mar 28 '16 at 00:44
Well, sadly it doesn't work. Apparently you can't have a reference to a group function in the `ORDER BY` clause. I'm lost. – Kilgore Mar 28 '16 at 09:00
I tested my answer and it works well. Do you get an error from running the query? If yes, can you kindly post it? – Reversal Mar 28 '16 at 09:55
PhpMyAdmin responds with **#1247 - Reference 'Fullnumbers' not supported (reference to group function)**. It is the same as with my original query. So apparently you can refer to a group function in a `HAVING` clause, but not in an `ORDER BY` clause. – Kilgore Mar 28 '16 at 10:18
You can enclose your whole query in a second SELECT query and then use Fullnumbers as a normal field name. Check last edit – Reversal Mar 28 '16 at 14:59
It works well with an enclosing SELECT. Thanks a lot for your help! A small note though: `SQL_CALC_FOUND_ROWS` has to be moved to the outermost SELECT for the query to work. I'll accept your answer after a small edit. I'll use my original `ORDER BY` clause as it is shorter without IFs. I still don't quite understand why you can't refer to group functions in `ORDER BY` clauses, as I would have thought it should be the last part evaluated. – Kilgore Mar 28 '16 at 15:30
I tried to edit your answer and placed `SQL_CALC_FOUND_ROWS` correctly, but the edit was rejected. I only just changed my status as a long time SO lurker to being an active user, so I don't quite understand why. That part of the query has nothing to do with my question, but I feel it should be correct anyhow. – Kilgore Mar 28 '16 at 16:21

How do I order rows by number of fields matched when GROUP_CONCAT is used?

1 Answers1