MySQL Custom Sorting Rules

Today I was facing troubles with the administrative dept of the web agency where I’m working in… They wanted to have their receiptive structures sorted by a custom role…

What I first thought about was a MySQL Union query where I had to fetch all distinct types of receiptive structures (Hotels, B&Bs, Campings, etc…) in the desired order in different parametrized queries… Something like the following:

(
  SELECT
    1 as sorting_column,
    *
  FROM receiptive_structures
  WHERE type_id=4
) AS a
UNION ALL
(
  SELECT
    2 as sorting_column,
    *
  FROM receiptive_structures
  WHERE type_id=2
) AS b
UNION ALL
(
  SELECT
    3 as sorting_column,
    *
  FROM receiptive_structures
  WHERE type_id=7
) AS c
UNION ALL
(
  SELECT
    4 as sorting_column,
    *
  FROM receiptive_structures
  WHERE type_id=1
) AS d
ORDER BY sorting_column ASC

Althrough the ‘sorting_column’ shouldn’t be necessary, I had too many bad experiences with mysql and its sorting features (expecially when merging together resultsets, like in this case).

Nasty solution…
I decided to continue looking for a more elegant way of doing this…

I decided to use some kind of on-the-fly generated function in the ORDER BY clause of my MySQL Custom Sorting Query:

SELECT
((type_id=4)*1
+(type_id=2)*2
+(type_id=7)*3
+(type_id=1)*4) AS sorting_column,
r.*
FROM receiptive_structures AS r
ORDER BY sorting_column ASC

Still looks ugly…
Digging into the MySQL manual, found out that there’s a faster way of doing that!

SELECT * FROM receiptive_structures
ORDER BY FIELD(priority, 4,2,7,1);

And here’s a quick overview of the MySQL FIELD() “weight” function

Database changed
mysql> select id, FIELD(id,4,2,7,1) AS sorting
    -> FROM l10n ORDER BY sorting DESC limit 5;
+----+---------+
| id | sorting |
+----+---------+
|  1 |       4 |
|  7 |       3 |
|  2 |       2 |
|  4 |       1 |
|  3 |       0 |
+----+---------+
5 rows in set (0.00 sec)

I hope this will be useful for you