Let us suppose you have a table that has a column called state. Within that column you have the following distinct values:
- pending_approval
- approved
- rejected
Let us also assume that you want to order retrieved rows in the exact same order as I listed the items.
A regular
order by won't do. It will show the results alphabetically, either ascending or descending.Usually, I am inclined to prepend the values with sortable pieces of text such as 1_, however this messes up the semantics of the value. Doing this makes me feel dirty.
Well, what do you know, SQL actually took this scenario into account:
select * from table
order by case state
when 'pending_approval' then 0
when 'approved' then 1
when 'rejected' then 2
end;
What a gem!
The functionality goes quite deep, giving you even the option to fetch values from other columns.
Regrettably, this functionality cannot be accessed through JPA/Hibernate. My good colleague and database prima donna, Ron Smeets, advises to lay a view upon the table. Interesting idea, though for the specific use case I am looking at, this is not an option. This probably means going back to using JDBC.
As far as I am concerned, certainly goes to show that the database layer still holds treasures for application developers to discover.
In a sense it also shows that SQL has way too many features that are used seldom and complicate the specs. An alternative way to solve this issue would have been to add an extra table named `state_ordering' with these contents:
ReplyDeletepending_approval, 0
approved, 1
rejected, 2
Then you could have joined and sorted on that extra table. It even works with JPA and Hibernate..
Verily so, however the solution you propose requires an update mechanism to keep both columns always in sync. Either a trigger, or using a custom mapper to split the enum value over two database columns. I guess I would prefer the latter.
ReplyDelete