Is ordering by "custom order" possible?

Chupacabras22 · #1 (**permalink**) 05-05-11, 08:16

I have this table (each row represents one document with title and status):

Code:

id(int), title(varchar), status(int)
------------------------------------
91,      docA,           1
92,      docB,           3
93,      docC,           2
94,      docD,           1
95,      docE,           4

and I need to sort it by status. Not to sort it numerically but alphabetically by names of those statuses.
Status names are different for every language.

English status names (for example):
1 = Draft
2 = For approval
3 = Approved
4 = Rejected

so correct alpabetical order of statuses is: 3, 1, 2, 4
and documents should be ordered like this:

Code:

id(int), title(varchar), status(int)
------------------------------------
92,      docB,           3
91,      docA,           1
94,      docD,           1
93,      docC,           2
95,      docE,           4

but another user uses let's say Slovak language, where statuses have different alphabetical order.

Slovak status names (for example):
1 = Navrh
2 = Na schvalenie
3 = Schvaleny
4 = Neschvaleny

order: 2, 1, 4, 3
(the order can be different for every other language)

so documents would be ordered like this (for slovak language):

Code:

id(int), title(varchar), status(int)
------------------------------------
93,      docC,           2
91,      docA,           1
94,      docD,           1
95,      docE,           4
92,      docB,           3

Is there any way how to do it in postgresql?

shammat · #2 (**permalink**) 05-05-11, 09:55

You can do something like this:

Code:

ORDER BY 
        CASE 
          when status = 3 then 1
          when status = 1 then 2
          when status = 2 then 3
          when status = 4 then 4
        END

But the better way would be to have another table that stores the status names and then join that to your query and sort by the status name directly.

Something along the lines:

Code:

SELECT st.id, 
       st.title, 
       st.status
FROM stuff st
  JOIN status_name sn ON sn.id = st.status AND sn.language = 'en'
ORDER BY sn.name

Chupacabras22 · #3 (**permalink**) 05-05-11, 10:18

Thank you.
That is exactly what I was looking for.

I have to think about those 2 ways and choose which one is better to implement.

Why do you think that the second option is the better one?

shammat · #4 (**permalink**) 05-05-11, 10:27

Quote:

Originally Posted by Chupacabras22

Why do you think that the second option is the better one?

Because you don't need to hardcode the sort order. And you don't need to worry about new languages. And you don't need to worry about adjusting your queries (the order by case) when you change the name of a status.

Chupacabras22 · #5 (**permalink**) 05-05-11, 10:36

Well, I would never hardcode such thing.

It is J2EE web app.
Every translation is in its properties file. Status names are translated too and I am able to easily pick them from property file. Then sort them and build proper "ORDER BY CASE..."

I am using hibernate, so I have to decide which one of those tho ways is better for me.

shammat · #6 (**permalink**) 05-05-11, 10:58

I still think putting the status name into the database is easier especially from a maintenance point of view.

Image what happens if a new status is introduced: with the second solution you insert a new row (for each language) and you are done. No need to worry about anything.

And you can create a foreign key to ensure that only valid status values are used.

Chupacabras22 · #7 (**permalink**) 05-05-11, 11:21

I understand you.

But the first solution is worry-free too (I don't prefer it, it is still a tie

)
Status names are translated in property files. I could add/remove/correct status in properties and it wouldn't break anything.

If I choose the second solution I will depend on those property files too, because on every deploy/start of web application I would need to delete all rows in status_names_table, read status names from property files and recreate them into the table (this assures that table is filled by up-to-date status names). Otherwise I would have to add/remove/correct names in that table manually (what is waste of time and a potential source of problems).

I guess I will try both ways and see which one brings less troubles.

Internet Services

Web Development

Digital Marketing

Consumer Tech