working with databasejoin and handle jtext

MeAppBuilder

MeAppBuilder

Member
Hello,

I have created a table for a select field. When I choose databasejoin, select will return...

value => id
label => jtext

For my "jtext" field I am using labels such as FABRIK_LABEL_SOME_OPTION. The select will return the string, but no translation. I know about hacking with two columns for each language and filter based on language, but it would be nice to have an option for translating jtext (so all translations are available in override).
 
this "feature" would effect the way I am currently developing. Therefore I have to make a decision how to set up the select tables in db. For now I could use a simple dropdown. For future I would like to use databasejoin to speed up the form.
 
solution with 1 table for translating the databasejoin labels...here we go:

I have 1 table where I can edit default, english and german translation. I have also a sort and publish field in addition to my translations. If you want to do more, you could implement a fallback to default in case de or en string is empty:

Code: 
CREATE TABLE `x_table_select` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `published` int(1) DEFAULT NULL,
  `default` varchar(255) DEFAULT NULL,
  `de` varchar(255) DEFAULT NULL,
  `en` varchar(255) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Daten f?r Tabelle `x_table_select`
--

INSERT INTO `x_table_select` (`id`, `default`, `de`, `en`, `sort`, `published`) VALUES
(1, 'val0 default', 'val0 de', 'val0 en', 0, 1),
(2, 'val1 default', 'val1 de', 'val1 en', 10, 1),
(3, 'val2 default', 'val2 de', 'val2 en', 10, 1);

I will make use of the concat function on my ELEMENT (databasejoin), that looks into my "x_table_select" for all available options to pull based on the current active language (see attached screenshot):

Code below Updated...

Code: 
(SELECT CASE WHEN BINARY '{lang}' = 'de_DE' THEN {thistable}.de WHEN BINARY '{lang}' = 'en_GB' THEN {thistable}.en ELSE {thistable}.default END FROM {thistable} WHERE id = value),''

For "Joins where and/or order by statement (SQL)":

Code: 
WHERE published = 1 ORDER BY {thistable}.sort ASC, text ASC

This will sort list by column "sort" at first and within this sorting by the actual string that is displayed within the databasejoin field (therefore based on the correct language).
 

Attachments

  • lang-label-databasejoin.jpg
    lang-label-databasejoin.jpg
    151.5 KB · Views: 214
Last edited:
too bad...using this approach will cause a list view return no data on my main table that is related to the databasejoin field...it is a list of Joomla users...works when I disable those databasejoin fields...will do some more testing.

Edit: I had an auto-complete filter still active with element.
 
Last edited:
Did you try to use the language strings in your dbjoon and use the Advanced options to send this text through JText:: (or FText::)?

gesendet mit Tapatalk
 
I have tried another concat statement, which will work as well on the databasejoin dropdown field in my form on frontend, but fails for a list in backend (because column "value" is of course unknown).

Code: 
(SELECT CASE WHEN BINARY '{lang}' = 'de_DE' THEN {thistable}.de WHEN BINARY '{lang}' = 'en_GB' THEN {thistable}.en ELSE {thistable}.default END FROM {thistable} WHERE id = value),''

I will check out your hint next and might go on with the second table solution for labels as mentioned in the wiki in case I can't come up with a solution.

Thanks
 
Last edited:
Instead of using "concat label" I just used a column as label that contains a string. In backend list there are no results as well, in frontend my databasejoin dropdown list will show this string (that would be translated using the Advanced section...haven't tried the translation yet)
 
using my approach with inner select everything works fine on the fronend, but in backend viewing the list I get an error because of my "value" field that is essential for the databasejoin to map the correct id. I would love anybody who could help me out here:

Code: 
Unknown column 'value' in 'where clause' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `sc_user`.`id` AS `sc_user___id`, `sc_user`.`id` AS `sc_user___id_raw`, `sc_user`.`gender` AS `sc_user___gender`, `sc_user`.`gender` AS `sc_user___gender_raw`, `sc_user`.`first_name` AS `sc_user___first_name`, `sc_user`.`first_name` AS `sc_user___first_name_raw`, `sc_user`.`last_name` AS `sc_user___last_name`, `sc_user`.`last_name` AS `sc_user___last_name_raw`, `sc_user`.`username` AS `sc_user___username`, `sc_user`.`username` AS `sc_user___username_raw`, `sc_user`.`group` AS `sc_user___group`, `sc_user`.`group` AS `sc_user___group_raw`, `sc_user`.`sport` AS `sc_user___sport_raw`, CONCAT_WS('', (SELECT CASE WHEN BINARY 'en_GB' = 'de_DE' THEN `sc_user_select_sport`.de WHEN BINARY 'en_GB' = 'en_GB' THEN `sc_user_select_sport`.en ELSE `sc_user_select_sport`.default END FROM `sc_user_select_sport` WHERE id = value),'') AS `sc_user___sport`, `sc_user`.`name` AS `sc_user___name`, `sc_user`.`name` AS `sc_user___name_raw`, `sc_user`.`email` AS `sc_user___email`, `sc_user`.`email` AS `sc_user___email_raw`, `sc_user`.`birthdate` AS `sc_user___birthdate`, `sc_user`.`birthdate` AS `sc_user___birthdate_raw`, `sc_user`.`password` AS `sc_user___password`, `sc_user`.`password` AS `sc_user___password_raw`, `sc_user`.`block` AS `sc_user___block`, `sc_user`.`block` AS `sc_user___block_raw`, `sc_user`.`datetime_register` AS `sc_user___datetime_register`, `sc_user`.`datetime_register` AS `sc_user___datetime_register_raw`, `sc_user`.`ip` AS `sc_user___ip`, `sc_user`.`ip` AS `sc_user___ip_raw`, `sc_user`.`id` AS slug , `sc_user`.`id` AS `__pk_val` FROM `sc_user` LEFT JOIN `sc_user_select_sport` AS `sc_user_select_sport` ON `sc_user_select_sport`.`id` = `sc_user`.`sport` WHERE 1 = -1 LIMIT 0, 10
 
When I change my inner select "WHERE id = value" to "WHERE id = 1" for example...at least backend list is not producing an error. But there are no results in list view.

Update:

the reason I don't get a result is the WHERE statement at the end:

Code: 
WHERE 1 = -1

not sure why this is in the query, but another user report something similar: http://fabrikar.com/forums/index.php?threads/prefilter-query-misbehaving.44340/#post-227935

And this causes the list to return zero data.
 
Last edited:
There's a couple of cases when a query will add that. If it's the main list getData query, it's because not all of the "required filters" are present. Either individual required element filters, or the overall list "require filter".

-- hugh
 
very good hint...I will check that out...meanwhile...how can I get around my concat label select statement for list:

my WHERE with this code should work on a list view as well, but doesn't...because I can't use "value"

Code: 
WHERE {thistable}.id = value

Code: 
(
SELECT
CASE
WHEN BINARY '{lang}' = 'de_DE' THEN {thistable}.de
WHEN BINARY '{lang}' = 'en_GB' THEN {thistable}.en
ELSE {thistable}.default
END
FROM {thistable}
WHERE {thistable}.id = value
),''
 
your hint was perfect...i missed an auto-complete filter with my databasejoin element that was still active. Thank you, thank you...list view is working. Banged my head for half a day.
 

Attachments

  • list-view.jpg
    list-view.jpg
    88.8 KB · Views: 166
Now all I need is the correct concat statement for my databasejoin element, so it will work with my frontend form element (with my code it does) and with my backend view list (produces error because of the "value" column)

Code: 
(
SELECT CASE 
WHEN BINARY '{lang}' = 'de_DE' THEN {thistable}.de
WHEN BINARY '{lang}' = 'en_GB' THEN {thistable}.en
ELSE {thistable}.default
END
FROM {thistable}
WHERE {thistable}.id = value
),''
 
not sure what you are doing, but you should at least quote value with back ticks `value`. It's a column name.

gesendet mit Tapatalk
 
I think I lost myself with this approach. I don't want to use 2 tables for my translations on dropdowns.

I thought about your solution using PHP and JTEXT or FTEXT handling. It might actually be the best solution. I will still have 1 table for 1 databasejoin and I can maintain translations within my language files. I will try this when I get back from running.
 
@troester

fantastic...it's working

last thing to do is have the second ORDER BY statement apply to FTEXT translation instead of the actual string in database. Not sure how that could be done:

Solution:

Table

Code: 
CREATE TABLE `x_table_select` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ftext` varchar(255) DEFAULT NULL,
  `published` int(1) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Daten f?r Tabelle `x_table_select`
--

INSERT INTO `x_table_select` (`id`, `ftext`, `published`, `sort`) VALUES
(1, 'FABRIK_LABEL_x', 1, 1),
(2, 'FABRIK_LABEL_a', 1, 10),
(3, 'FABRIK_LABEL_b', 1, 10),

Details->Data->Or Concat Label

Code: 
({thistable}.ftext),''

Details->Data - where->Joins where and/or order by statement (SQL)

Code: 
WHERE published = 1 ORDER BY {thistable}.sort ASC, text ASC

Details->Advanced->Eval options

Code: 
$opt->text = FText::_($opt->text);

and don't forget to add ftext labels to Joomla language override
 
Yeah, ordering and searching by the translated field isn't possible.

We can't even do it by adding code to do the ordering / searching at the application level once rows have been read from the table, because pagination isn't possible that way ... the only way to do it would be to select ALL the rows from the table, translate every field, then do searching, ordering, pagination in memory. Which isn't going to happen. :)

So it's a trade off. Go Jaanus's route of having a lookup table, with multiple tables for the translations ... which allows ordering but has the overhead of multiple table, complex CONCAT's, etc. .... or go the FText route, which is way simpler, but can't be ordered or searched.

-- hugh
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade

    • Members online

    No members online now.
    Total: 27 (members: 1, guests: 26)
    Back
    Top