I
ittay.dror
Hi,
I keep running into a problem of how to bridge how data is saved in the
DB and how it is presented to the user, especially when sorting lists.
Say I have a table named users. it has all the usuall columns (id, name
etc.) and in addition a status, that can be 1 for active user, 2 for
suspended user and 3 for deleted user (this is of course a dummy
example, invent any enumeration you want). The user is presented with a
list of users, and in the column "Status", the strings "Active",
"Suspended" and "Deleted" are used.
Now, I want to let the user sort by the status column. Since I have
many users, I want the sorting to be done in the DB, and only part of
the data (current page) to be returned. However, sorting by the status
column will return Active users first, then Suspended, and only then
Deleted. Not the order one would expect.
I can of course solve it trivially by exchanging the meaning of '2' and
'3' in the column. But then what will happen if I want to translate to
other languages? Another option is to maintain a gui_status column in
the DB, but then the point of MVC is broken, and again, what about
translations? I can also load all rows, create records with the gui
names instead and sort in memory, but that is very expensive.
To make the problem more complex, sometimes several columns are
aggregated as one column in the gui.
So my question is whether there is a known idiom to handle such cases.
Thank you,
Ittay
I keep running into a problem of how to bridge how data is saved in the
DB and how it is presented to the user, especially when sorting lists.
Say I have a table named users. it has all the usuall columns (id, name
etc.) and in addition a status, that can be 1 for active user, 2 for
suspended user and 3 for deleted user (this is of course a dummy
example, invent any enumeration you want). The user is presented with a
list of users, and in the column "Status", the strings "Active",
"Suspended" and "Deleted" are used.
Now, I want to let the user sort by the status column. Since I have
many users, I want the sorting to be done in the DB, and only part of
the data (current page) to be returned. However, sorting by the status
column will return Active users first, then Suspended, and only then
Deleted. Not the order one would expect.
I can of course solve it trivially by exchanging the meaning of '2' and
'3' in the column. But then what will happen if I want to translate to
other languages? Another option is to maintain a gui_status column in
the DB, but then the point of MVC is broken, and again, what about
translations? I can also load all rows, create records with the gui
names instead and sort in memory, but that is very expensive.
To make the problem more complex, sometimes several columns are
aggregated as one column in the gui.
So my question is whether there is a known idiom to handle such cases.
Thank you,
Ittay