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