Filtering + Data Localisation & Formatting

Hi Guys,

I am sure this is a common issue, but cannot get a good way of thinking about it.

We have a frontend where users can view, filter data etc.

Sometimes the data is formatted to make more sense, e.g. error_level is 0 and shown as “Warning”. etc.

The data is also localised when it makes sense.

Filter requests are passed through the API, translated into SQL, and executed on the DB.

The aim is to filter localised and formatted data.

Here are a few ideas:

  1. Show ‘options’ in frontend, i.e. HTML select to do the formatting and localisation etc.
  • problem, only works for a knowable finite list of options
  1. do all localisation / formatting in database view (the ‘Joe Armstrong option’)
  • localisation tools aren’t readily available
  1. do localisation / formatting in backend / frontend
  • effectively have to duplicate filtering functionality in backend or frontend

Any thoughts, more ideas? Perhaps I am thinking too much about this, and the answer is obvious…

In my opinion, localization is totally frontend’s responsability. It’s basically how you present your data to different people, so I don’t think duplicating it to backend is a good idea.

Your solution #1 is what I would go to first. About the finite list of options problem: what is the case here? I don’t get why would you have an infinite list of options on a select for your filter.

That’s a v good pt.

I think the reason for localisation on the backend, is:

filtering in a foreign language.

If I am in Germany and want to search for “zu*” it won’t match “train”.

And… you cannot filter over too much data purely in the frontend…


I see, but the thing is: aren’t these data inserted by the user himself? If the user inserts “Zug” for the register, when he filters for “zu*” he will get that register.

I get the problem if you want your system to get multiple languages inside the same tenant for example. So a german user would see “Zug” on that register, and a american one would see “Train” on the same one. But then you would need to have a way to translate registers, and with that, your database queries will get very complex. In this case the database views are indeed a very good option, if not the best, but filtering will not be your only problem.

I would REALLY consider the possibility of not adding that kind of complexity to an app.

1 Like

Unfortunately not.

And unfortunately an unavoidable requirement…

Yet I am sure this is not too uncommon, just cannot figure out the name for the principle / heuristic / trick I need to use : )

V good pt - of course sorting is also problematic… : /

I see, but what not? Is it not inserted by the user? Or is it inserted by the user but you still need to translate the registers?

If it’s not inserted by the user (e.g. you control the table) you could add a “locale” field to the table, duplicate registers for each locale and filter it by the current user locale.

If the case it’s the other one I mentioned previously, I would create a translations table, join it to the queries and filter that. But also the user would have to translate the register himself.

Another possibility would be to transform the translatable fields into json fields with the key being the locale and the value the translated value. You would have to use fragment to query, but no joins would be needed. Just don’t forget to fallback to a default language (or one that jas a value) when the user does not have the translations for that locale.

And that’s another thing, in this case the user will be responsible for translating it himself. But you probably already know that. :slight_smile:

In this case, it’s about many different types of system events, which explain what is happening.

The users need to have events translated so they understand.

Just an icky feature.

I agree with your focus on doing things in DB - and think ‘the Joe Armstrong’ way could be the best-worst option.

1 Like

So yeah, as they are controlled by you, I would just add a locale with it on the db.

Basically the rule I set for myself is: however you save the descriptions, the translations should be saved in a compatible manner. So if the origin of the translated strings is the DB, the translation should be there too, and backend should handle it. If the origin is hardcoded, then the translations can be hardcoded too, but I would handle it on frontend then.