Databasejoin: second element with a joined label doesn't work

[Roland]

Hi Hugh,

if I try to define two or three elements as databasejoin onto the same table and within the same form, I get a database error on the second field if I configure the label as a "joined label".
If I use the option label from one database field from the dropdown list, it works.
The error doesn't result from a wrong definition in my specifications.
I defined it as: jos_npl_adressen.Name, ' ', jos_npl_adressen.Vorname
I can proof the specifications by deactivating two of the three database fields that I have to integrate in my form. Each of them works if it is the only field defined as label from database field.
The necessarity to use label from database field is, that I have to show personal names consisting of first name and last name. To pick just the last name is not enaugh information for the user as there might be the same names.

Error message:
1054 Unknown column 'jos_npl_adressen.Name' in 'field list' SQL=SELECT DISTINCT(`jos_npl_adressen_0`.`id`) AS value, CONCAT_WS('', jos_npl_adressen.Name, ' ', jos_npl_adressen.Vorname) AS text FROM `jos_npl_adressen` AS `jos_npl_adressen_0` WHERE Ampel = 'gruen' ORDER BY text ASC

My conclusion is that Fabrik adds a string to the name of the table and tries to access anot existing table. In this case "_o" is added. In other constellations "_1" was added.
If I remove "_o" the queries work via SQL.

Could you check it please?

Best regards
Roland

 

[cheesegrits]

You can't use literal table names in your "concat label", if you have more than one join to the same table, for exactly that reason. Obviously if you have more than one join to the same table, we have to use table aliases, like "LEFT JOIN foo AS foo_0 ... LEFT JOIN foo AS foo_1 ...", otherwise the query will blow up with ambiguous table references. And of course there is no way of knowing ahead of time what a given alias will be.

So ... we have a workaround for that, which should be detailed in the tooltip (but may not be in your language, I don't know) which is the special placeholder {thistable}, which we will replace with the correct alias for the table you are joining to, when the query is built. So instead of jos_npl_adressen.Name, use {thistable}.Name, which we will then replace with jos_npl_adressen_0 or jos_npl_adressen_1, etc as appropriate.

-- hugh

 

[Roland]

Thank you Hugh,
the workaround is exactly what I needed.
My fault, as I can see the tooltip in English. It is small and vanishes easily on mouseover but it says clearly:
"Use {thistable} placeholder to ensure that you use the correct table name (which can be suffixed with '_n' in the case of more than one join to the same table)"
I did not understand that the bolded text ist meant for more than one Fabrik elements.

Roland