Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    4

    Question Unanswered: Is ordering by "custom order" possible?

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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

  3. #3
    Join Date
    May 2011
    Posts
    4
    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?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Chupacabras22 View Post
    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.

  5. #5
    Join Date
    May 2011
    Posts
    4
    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.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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.

  7. #7
    Join Date
    May 2011
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •