Forums

English
Hello
I have a problem when reading information between 3 tables. I want to retrieve certain information from a user ("users" table) as well as the values and labels of the special fields added to this table ("fields_values" and "fields" tables).
I only want to recover the label of the special field, not the other information, so only the "title" field of the "fields" table.
So I indicated the value "champlibelle.title" in the "Fields to retrieve" area (I also tried the simple value "title" without "champlibelle"), same result...
The generated SQL is false (i get all the columns from the "fields" table) ! Why ? I do not know ?
Here is the select generated by the "Debug" as well as the configuration of the 3 tabs of the "Read Data".
Important information: I did not select the "This is an extra fields list" parameter
​
Français
Bonjour
Je rencontre un problème lors de la lecture d'informations entre 3 tables. Je désire récupérer certaines informations d'un utilisateur (table "users") ainsi que les valeurs et les libellés des champs spéciaux ajoutés à cete table (tables "fields_values" et "fields").
Je ne désire récupérer uniquement que le libellé du champ spécial, pas les autres informations, donc uniquement le champ "title" de la table "fields".
J'ai donc indiqué la valeur "champlibelle.title" dans la zone "Fields to retrieve" (j'ai égalemet essayé la simple valeur "title" sans "champlibelle"), même résultat...
Le SQL généré est faux (je récupère toutes les colonnes de la table "fields") ! Pourquoi ? Je ne sais pas ?
Voici le select généré par le "Debug" ainsi que le paramétrage des 3 onglets du "Read Data".
Information importante : je n'ai pas sélectionné le paramètre "This is an extra fields list"
​
SELECT / DEBUG

Array
(
    [Itemid] => 599
    [option] => com_chronoforms6
    [view] => form
)

Array
(
    [utilisateur_lire] => Array
        (
            [log] => Array
                (
                    [0] => SELECT `utilisateur`.`id` AS `utilisateur.id`, `utilisateur`.`name` AS `utilisateur.name`, `utilisateur`.`username` AS `utilisateur.username`, `utilisateur`.`email` AS `utilisateur.email`, `utilisateur`.`password` AS `utilisateur.password`, `utilisateur`.`block` AS `utilisateur.block`, `utilisateur`.`sendEmail` AS `utilisateur.sendEmail`, `utilisateur`.`registerDate` AS `utilisateur.registerDate`, `utilisateur`.`lastvisitDate` AS `utilisateur.lastvisitDate`, `utilisateur`.`activation` AS `utilisateur.activation`, `utilisateur`.`params` AS `utilisateur.params`, `utilisateur`.`lastResetTime` AS `utilisateur.lastResetTime`, `utilisateur`.`resetCount` AS `utilisateur.resetCount`, `utilisateur`.`otpKey` AS `utilisateur.otpKey`, `utilisateur`.`otep` AS `utilisateur.otep`, `utilisateur`.`requireReset` AS `utilisateur.requireReset`, `champvaleur`.`field_id` AS `champvaleur.field_id`, `champvaleur`.`item_id` AS `champvaleur.item_id`, `champvaleur`.`value` AS `champvaleur.value`, `champlibelle`.`id` AS `champlibelle.id`, `champlibelle`.`asset_id` AS `champlibelle.asset_id`, `champlibelle`.`context` AS `champlibelle.context`, `champlibelle`.`group_id` AS `champlibelle.group_id`, `champlibelle`.`title` AS `champlibelle.title`, `champlibelle`.`name` AS `champlibelle.name`, `champlibelle`.`label` AS `champlibelle.label`, `champlibelle`.`default_value` AS `champlibelle.default_value`, `champlibelle`.`type` AS `champlibelle.type`, `champlibelle`.`note` AS `champlibelle.note`, `champlibelle`.`description` AS `champlibelle.description`, `champlibelle`.`state` AS `champlibelle.state`, `champlibelle`.`required` AS `champlibelle.required`, `champlibelle`.`checked_out` AS `champlibelle.checked_out`, `champlibelle`.`checked_out_time` AS `champlibelle.checked_out_time`, `champlibelle`.`ordering` AS `champlibelle.ordering`, `champlibelle`.`params` AS `champlibelle.params`, `champlibelle`.`fieldparams` AS `champlibelle.fieldparams`, `champlibelle`.`language` AS `champlibelle.language`, `champlibelle`.`created_time` AS `champlibelle.created_time`, `champlibelle`.`created_user_id` AS `champlibelle.created_user_id`, `champlibelle`.`modified_time` AS `champlibelle.modified_time`, `champlibelle`.`modified_by` AS `champlibelle.modified_by`, `champlibelle`.`access` AS `champlibelle.access` FROM `am_users` AS `utilisateur` LEFT JOIN `am_fields_values` AS `champvaleur` ON `utilisateur`.`id` = `champvaleur`.`item_id` LEFT JOIN `am_fields` AS `champlibelle` ON `champvaleur`.`field_id` = `champlibelle`.`id` WHERE `utilisateur`.`id` = '959' LIMIT 100;
                )

            [var] => Array
                (
                    [0] => Array
                        (
                            [utilisateur] => Array
                                (
                                    [id] => 959
                                    [name] => Jean-Marie GROUD
                                    [username] => jmgroud
                                    [email] => jmgroud@jmgi.fr
                                    [password] => $2y$10$To0yga8qFHA5hDKOfNNDWerkFUB6AucklPFBV9Ij0wxpMgTUh.Fee
                                    [block] => 0
                                    [sendEmail] => 0
                                    [registerDate] => 2017-03-25 12:44:40
                                    [lastvisitDate] => 2020-02-19 11:50:51
                                    [activation] => 
                                    [params] => {"admin_style":"","admin_language":"","language":"","editor":"","helpsite":"","timezone":""}
                                    [lastResetTime] => 0000-00-00 00:00:00
                                    [resetCount] => 0
                                    [otpKey] => 
                                    [otep] => 
                                    [requireReset] => 0
                                )

                            [champvaleur] => Array
                                (
                                    [field_id] => 3
                                    [item_id] => 959
                                    [value] => aaa_Personnel/Documents_Administration/Portraits/Portrait-Jean-Marie-GROUD-151x183.jpg
                                )

                            [champlibelle] => Array
                                (
                                    [id] => 3
                                    [asset_id] => 152
                                    [context] => com_users.user
                                    [group_id] => 2
                                    [title] => Photo
                                    [name] => photo
                                    [label] => Photo
                                    [default_value] => 
                                    [type] => media
                                    [note] => 
                                    [description] => 
                                    [state] => 1
                                    [required] => 0
                                    [checked_out] => 0
                                    [checked_out_time] => 0000-00-00 00:00:00
                                    [ordering] => 1
                                    [params] => {"hint":"","class":"","label_class":"","show_on":"","render_class":"","showlabel":"1","label_render_class":"","display":"2","layout":"","display_readonly":"2"}
                                    [fieldparams] => {"directory":"","preview":"","image_class":""}
                                    [language] => *
                                    [created_time] => 2017-09-18 15:58:19
                                    [created_user_id] => 959
                                    [modified_time] => 2019-08-23 17:44:29
                                    [modified_by] => 959
                                    [access] => 1
                                )

                        )

                    [1] => Array
                        (
                            [utilisateur] => Array
                                (
                                    [id] => 959
                                    [name] => Jean-Marie GROUD
                                    [username] => jmgroud
                                    [email] => jmgroud@jmgi.fr
                                    [password] => $2y$10$To0yga8qFHA5hDKOfNNDWerkFUB6AucklPFBV9Ij0wxpMgTUh.Fee
                                    [block] => 0
                                    [sendEmail] => 0
                                    [registerDate] => 2017-03-25 12:44:40
                                    [lastvisitDate] => 2020-02-19 11:50:51
                                    [activation] => 
                                    [params] => {"admin_style":"","admin_language":"","language":"","editor":"","helpsite":"","timezone":""}
                                    [lastResetTime] => 0000-00-00 00:00:00
                                    [resetCount] => 0
                                    [otpKey] => 
                                    [otep] => 
                                    [requireReset] => 0
                                )

                            [champvaleur] => Array
                                (
                                    [field_id] => 9
                                    [item_id] => 959
                                    [value] => +33 6 07 58 85 62
                                )

                            [champlibelle] => Array
                                (
                                    [id] => 9
                                    [asset_id] => 193
                                    [context] => com_users.user
                                    [group_id] => 2
                                    [title] => Téléphone mobile personnel
                                    [name] => telephone-mobile-personnel
                                    [label] => Téléphone mobile personnel
                                    [default_value] => 
                                    [type] => text
                                    [note] => 
                                    [description] => 
                                    [state] => 1
                                    [required] => 0
                                    [checked_out] => 0
                                    [checked_out_time] => 0000-00-00 00:00:00
                                    [ordering] => 2
                                    [params] => {"hint":"","class":"","label_class":"","show_on":"","render_class":"","showlabel":"1","label_render_class":"","display":"2","layout":"","display_readonly":"2"}
                                    [fieldparams] => {"filter":"","maxlength":17}
                                    [language] => *
                                    [created_time] => 2019-08-23 18:02:01
                                    [created_user_id] => 959
                                    [modified_time] => 2019-08-23 18:12:54
                                    [modified_by] => 959
                                    [access] => 1
                                )

                        )

                    [2] => Array
                        (
                            [utilisateur] => Array
                                (
                                    [id] => 959
                                    [name] => Jean-Marie GROUD
                                    [username] => jmgroud
                                    [email] => jmgroud@jmgi.fr
                                    [password] => $2y$10$To0yga8qFHA5hDKOfNNDWerkFUB6AucklPFBV9Ij0wxpMgTUh.Fee
                                    [block] => 0
                                    [sendEmail] => 0
                                    [registerDate] => 2017-03-25 12:44:40
                                    [lastvisitDate] => 2020-02-19 11:50:51
                                    [activation] => 
                                    [params] => {"admin_style":"","admin_language":"","language":"","editor":"","helpsite":"","timezone":""}
                                    [lastResetTime] => 0000-00-00 00:00:00
                                    [resetCount] => 0
                                    [otpKey] => 
                                    [otep] => 
                                    [requireReset] => 0
                                )

                            [champvaleur] => Array
                                (
                                    [field_id] => 13
                                    [item_id] => 959
                                    [value] => 1
                                )

                            [champlibelle] => Array
                                (
                                    [id] => 13
                                    [asset_id] => 198
                                    [context] => com_users.user
                                    [group_id] => 2
                                    [title] => Administrateur Chrono
                                    [name] => administrateur-chrono
                                    [label] => Administrateur Chrono
                                    [default_value] => 
                                    [type] => radio
                                    [note] => 
                                    [description] => Indique si cet utilisateur a les permissions d'administration de toutes les donnes de ChronoConnectivity et ChronoForms
                                    [state] => 1
                                    [required] => 0
                                    [checked_out] => 0
                                    [checked_out_time] => 0000-00-00 00:00:00
                                    [ordering] => 0
                                    [params] => {"hint":"","class":"btn-group","label_class":"","show_on":"","render_class":"","showlabel":"1","label_render_class":"","display":"2","layout":"","display_readonly":"2"}
                                    [fieldparams] => {"options":{"options0":{"name":"Oui","value":"1"},"options1":{"name":"Non","value":""}}}
                                    [language] => *
                                    [created_time] => 2020-02-12 14:08:09
                                    [created_user_id] => 959
                                    [modified_time] => 2020-02-12 14:36:03
                                    [modified_by] => 959
                                    [access] => 6
                                )

                        )

                )

        )

)

Images

Important information: I did not select the "This is an extra fields list" parameter
Information importante : je n'ai pas sélectionné le paramètre "This is an extra fields list"

[log] => Array
(
[0] => SELECT `utilisateur`.`id` AS `utilisateur.id`, `utilisateur`.`name` AS `utilisateur.name`, `utilisateur`.`username` AS `utilisateur.username`, `utilisateur`.`email` AS `utilisateur.email`, `utilisateur`.`password` AS `utilisateur.password`, `utilisateur`.`block` AS `utilisateur.block`, `utilisateur`.`sendEmail` AS `utilisateur.sendEmail`, `utilisateur`.`registerDate` AS `utilisateur.registerDate`, `utilisateur`.`lastvisitDate` AS `utilisateur.lastvisitDate`, `utilisateur`.`activation` AS `utilisateur.activation`, `utilisateur`.`params` AS `utilisateur.params`, `utilisateur`.`lastResetTime` AS `utilisateur.lastResetTime`, `utilisateur`.`resetCount` AS `utilisateur.resetCount`, `utilisateur`.`otpKey` AS `utilisateur.otpKey`, `utilisateur`.`otep` AS `utilisateur.otep`, `utilisateur`.`requireReset` AS `utilisateur.requireReset`, `champvaleur`.`field_id` AS `champvaleur.field_id`, `champvaleur`.`item_id` AS `champvaleur.item_id`, `champvaleur`.`value` AS `champvaleur.value`, `champlibelle`.`id` AS `champlibelle.id`, `champlibelle`.`asset_id` AS `champlibelle.asset_id`, `champlibelle`.`context` AS `champlibelle.context`, `champlibelle`.`group_id` AS `champlibelle.group_id`, `champlibelle`.`title` AS `champlibelle.title`, `champlibelle`.`name` AS `champlibelle.name`, `champlibelle`.`label` AS `champlibelle.label`, `champlibelle`.`default_value` AS `champlibelle.default_value`, `champlibelle`.`type` AS `champlibelle.type`, `champlibelle`.`note` AS `champlibelle.note`, `champlibelle`.`description` AS `champlibelle.description`, `champlibelle`.`state` AS `champlibelle.state`, `champlibelle`.`required` AS `champlibelle.required`, `champlibelle`.`checked_out` AS `champlibelle.checked_out`, `champlibelle`.`checked_out_time` AS `champlibelle.checked_out_time`, `champlibelle`.`ordering` AS `champlibelle.ordering`, `champlibelle`.`params` AS `champlibelle.params`, `champlibelle`.`fieldparams` AS `champlibelle.fieldparams`, `champlibelle`.`language` AS `champlibelle.language`, `champlibelle`.`created_time` AS `champlibelle.created_time`, `champlibelle`.`created_user_id` AS `champlibelle.created_user_id`, `champlibelle`.`modified_time` AS `champlibelle.modified_time`, `champlibelle`.`modified_by` AS `champlibelle.modified_by`, `champlibelle`.`access` AS `champlibelle.access` FROM `am_users` AS `utilisateur` LEFT JOIN `am_fields_values` AS `champvaleur` ON `utilisateur`.`id` = `champvaleur`.`item_id` LEFT JOIN `am_fields` AS `champlibelle` ON `champvaleur`.`field_id` = `champlibelle`.`id` WHERE `utilisateur`.`id` = '959' LIMIT 100;
)

Put the fields you want to retrieve in the FIRST model's fields to retrieve section.

English
Thank you healyhatman, you're the best ! 🙂
I haven't done it yet, but here I want to say a big thank you for all the investment you have to have to respond so kindly to all our problems ...
You are very present on the forum and if I have well understood you are not developer nor administrator of the site !!!
It is exceptional to have a person of this quality and also available.
So for me and everyone else a big general thank you !
​
There are a lot of explanations on how to save the additional fields (I haven't tested yet) but few basic explanations on how to configure the "read-data" to get the information from the main table and the other information stored in the "fields_value" and "fields" tables associated with this main table.
I will do some additional tests with a concrete display example (no storage) with the users so as to have a precise example and I will post this example here.
There may be (certainly) optimizations and everyone can then make comments.
​
Français
Merci healyhatman, tu es le meilleur ! 🙂
Je ne l'ai pas encore fait mais, je désire ici te dire un grand merci pour tout l'investissement que tu dois avoir pour répondre si gentillement à tous nos problèmes...
Tu es très présent sur le forum et si j'ai bien compris tu n'est pas développeur ni administrateur du site !!!
C'est exceptionnel d'avoir une personne de cette qualité et aussi disponible.
Donc, pour moi et pour tous les autre un grand merci général.

Il existe pas mal d'explications sur la façon de sauvegarder les champs additionnels (je n'ai pas encore testé) mais peu d'explications de base sur la façon de paramétrer les "read-data" pour obtenir les informations de la table principale et les autres informations stockées dans les tables "fields_value" et "fields" associées à cette table principale.

Je vais faire quelques tests complémentaires avec un exemple concret d'affichage (pas de stockage) avec les utilisateurs de façon à avoir un exemple préçis et je posterai ici cet exemple.
Il y aura peut-être (certainement) des optimisations et chacun pourra alors y apporter ses commentaires.