DBJoin label translation ?

[lcollong]

Hi,

Related to this post, I've done an interesting test for which I'd like to have your opinion.

I'm in the process to replace the use of core Dropdown, Radio and Checkbox elements by their "joined" counterpart in order to avoid storing user data in the core fabrik tables and ease statistics built based on these kind of fields.

So I have a "referential table" used to store the labels of, saying 3 dropdown elements rendered as DBjoin. This table has "id", "element_id", "label", "published" and "order" columns. I use "element_id" to limit the dbjoin to display only the appropriate values of a given dropdown element. "Id" is the "value" stored in the main DB table for each dropdown.

In most of the cases, we use DBjoin elements to display fields from a relevant table. There are no reasons to translate these fields. For example, in you have a list of cars with a trade element joined to an external list of trademarks, no reason to translate the labels.

But for a "referential table" used to store labels such I've exposed previously, there is a big interest to translate them. For examples, if these 3 dropdown relate to "body shape", "type of engine" and "color", translate these labels make sense for a a multi-language site.

There are several ways to deal with translations as explained by several Fabrik's powerusers. However the standard J! language override feature sounds to me very comfortable and efficient. It works very well for core Fabrik's dropdown labels but not for DBjoin ones.

So I dig in the source and copy the following lines before the "switch" instruction around line 1360 of the plugins/fabrik_element/databasejoin/databasejoin.php file :

foreach ($tmp as &$label_tmp)
{
    $label_tmp->text = FText::_($label_tmp->text);
}

And it does the trick ! whenever I change the language, my DBjoin dropdown shows the right translation.

I'm sure this is not the right place to do it (view or model ?) and I think it should be associated with an individual element parameter to actually override or not (default). Most of the case it's not a good idea to translate nor to "loose" CPU time of doing it.

Is this sound doable ? I'm pretty sure this complex element has "side effect" (concat label, access level, detail rendering, filter, etc....) that may make this enhancement more complicated than I expect.

Any thoughts ?

 

[cheesegrits]

That'd work for rendering editable joins in a form view, but you'd still need to do the translation for detail view and where the element is not editable, in the section above that. And of course for list view, in renderListData().

Then there's the whole issue of filtering and ordering, which would of course be working on the raw table data, not translated data. Filtering would probably be OK for 'dropdown' filters, as we work with the keys, not the labels for that. But for text / autocomplete stuff ... problematic. Very. I can't even think of a way to handle that, which didn't involve horrific layers of complexity which just wouldn't be realistically viable for us.

But for limited use in specific circumstances, you could do this. But it's not something I'd want to merge into the main code for a while, until you've done a lot more testing.

What I'd suggest is that you fork a copy on github, and set up a local repo, which you can then keep up to date by merging upstream changes as we make them. That way when you have something workable, it'll be 100x easier to get it PR'ed back in to the master.

I would also advise adding a param to the join element, to turn the feature on and off, and wrap conditions round anywhere you do this stuff. If we did merge it in, it'd be something we'd want turned off by default (as you say, it could be quite an overhead).

One approach I did think about a while back was allowing for dynamic table names. So the target table for the join is computed at runtime, like fab_{language}_foo. But although that solves the filtering, autocomplete, ordering, grouping, etc ... it raises it's own set of nasty issues, in how we then handle the metadata in things like #_fabrik_joins and #_fabrik_lists.

-- hugh

 

[lcollong]

I knew that it gonna be more difficult than I could imagine. However, as the "translation feature" is reserved to a limited subset of the general use of DBjoin, I have the feeling that filtering could be limited (from the translation point of view) to dropdown.

I will follow your advice on the fork for testing and additional param. As I've never setup such a thing. Is it better to install github/repo on my linux server or on a Windows machine ?

We'll see if this approach could be a long term solution or if you rather prefer to implement the idea you've mentioned.

 

[Jaanus]

It seems we haven't described this very trivial trick in wiki.

The "referential table" is basically the right way. The simplest way is to create own referential table per each main table that the dbjoin or CDD points to.
In this case the table (let's call it main) could even have only pk field (id), the referential table (say labels) will have fields like main_id, lang, label where values in field lang are language sef codes (like 'fr' or 'en')
In this case the dbjoin element is pointing to the table main, the value field is id and for the label you will use "or concat label" option:
(SELECT labels.label FROM labels WHERE labels.lang = '{lang}' AND labels.main_id = {thistable}.id)
{lang} works when your site is in multilingual mode
You create also a Fabrik list on the main table, there join the labels table using list join and make it repeated. Set the lang element as dbjoin pointing to the sef field of #__languages table (or use e.g dropdown field, there is also an option to populate it with db query results using php). Now you can manage the multilingual data in database without any ini files.

Yes, it would be possible to use even a single, common table to the translated dbjoin/CDD labels. There would be an additional field for main_table_name and you have to add "labels.main_table_name = 'main' " in your WHERE statement

 

[cheesegrits]

Can you put that in the wiki?

-- hugh

 

[lcollong]

Indeed. Thanks.

 

[MeAppBuilder]

Don't use "order" column...use "sort"...because "order" is SQL specific and will not work

ORDER BY order

 

[MeAppBuilder]

Solution for 1 table that will handle translations with 1 data row for a specific option: http://fabrikar.com/forums/index.php?threads/working-with-databasejoin-and-handle-jtext.44532/