Order by localized field

[6年前 に Roman Novikov によって更新されました。]

Hello!
Is any proper way to get records ordered by localized field corresponding locale from a table, other than just getting all the table data into a list, extracting the localized value and sorting by Collection.sort() ?
I know it can be done by custom finder, and I got a useful "select" for PostgreSQL. For example:

select
(select c.goods_content[(select b.rn from
(select row_number() over () rn, a.lang lang from
(select unnest(xpath('/root/Content/@language-id', xmlparse(document a.content)))::varchar lang
from eshop_goods a
where a.goodsId = d.goodsId) a) b
where b.lang='ru_RU')]::varchar from
(select xpath('/root/Content/text()', xmlparse(document a.content)) goods_content
from eshop_goods a
where a.goodsId = d.goodsId) c) localized
from eshop_goods d
order by 1 desc;

It's a bit complicated but works properly.
But I need to keep database interdependency feature. So does LIferay have such a specific functionality on this count?

[6年前 に Olaf Kock によって更新されました。]

With a significantly large table, this will probably fall flat on its face... I'm not aware of anything built into Liferay. In fact, a lot of filters within Liferay explicitly are not executed on the database, but on the search index. Maybe that interface (provided you're indexing your custom data types) gives you the options that you need.

Alternatively, you might want to choose not to implement your entity with a localizable field, but rather through either discreet columns or 1:n relationship to a translation table. After all, the localizable field is just one option to implement the feature, and the necessity to search for specific values could be a good reason to not use it (if you also can't use the full text index)

[6年前 に Roman Novikov によって更新されました。]

Olaf, thanks for your help! Yes, I have to use index for now.. At the other parts of the project I use sorting with List..
But I think it would be useful tool which make possible to sort localized data by database engine.. And it is possible and not so difficult... For example, my code above. This could be done also for other databases: Oracle and MySQL have their own support for XML inside SQL-queries. As for HSQL - it has a possibility to use custom Java classes to extend it's procedural language..