WoltLab/WCF — Issue #488: Sorting multilingual content
Author: Gravatronics
Created: 2012-03-03 12:57:56+00:00
State: open

Have you thought about how to handle sorting of multilingual content, like the group list?

Currently sorting by the groupName using the current implementation makes non sense, since it implicitly sorts by the groupID, since the groupName is `wcf.acp.group.group{$groupID}` (except for new groups with a monolingual name). Currently just the group list is affected (hopefully, the package list is also affected very soon ;)), but there might be others, especially in plugins and applications by other developers.

The problem is that getting the value in the user's language currently is nearly impossible, way back I implemented a query that joins the language item table with building the name of the language item with the id of the main table which, of course, was very slow. The only real alternative I can think of, is creating a new database table

```sql
DROP TABLE IF EXISTS wcf1_i18n_value;
CREATE TABLE wcf1_i18n_value (
i18nValueID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
packageID INT(10) NOT NULL,
objectTypeID INT(10) NOT NULL,
objectID INT(10) NOT NULL,
languageID INT(10) NOT NULL,
name VARCHAR(255) NOT NULL,
value MEDIUMTEXT,
KEY (objectTypeID, languageID, name)
);
```
which stored localized object properties. Here you could easily do a join with a fixed `objectTypeID`, `languageID` and `name` while `objectID` would have to mach the id of the main table. I rudimentally tested it for group (and package, c.f. other pull request) names and it works.

Of course, there would be the disadvantage that such data is stored twice: In the language item table and in this one. But then again, this might also be handy when searching for multilingual input, like in the help system which currently has an own "search engine" or in some sort of multilingual content management system.

May-be someone else has a better idea.