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).
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