Forums

ChronoForms v8 is now WordPress ready, ChronoMails is out

The JED needs volunteers!

DB Read Relations

re

relliott 29 Jan, 2016

Hello,

I am trying to join 2 tables using the DB read action. I have successfully joined the 2 tables but the array does not group under the Model ID on the "Basic" tab.

This is what my debug output looks like:

Array
(
    [option] => com_chronoforms5
    [view] => form
    [Itemid] => 113
    [0] => Array
        (
            [repgroup] => Array
                (
                    [user_id] => 490
                    [group_id] => 12
                )

            [repname] => Array
                (
                    [name] => Ryan Elliott
                )

        )

    [1] => Array
        (
            [repgroup] => Array
                (
                    [user_id] => 494
                    [group_id] => 12
                )

            [repname] => Array
                (
                    [name] => Dealer Demo Account
                )

        )

)

I am trying to display the data in a dynamic drop down list but have been unsuccessful in getting it to populate. What should the "Data Path" be on the element settings for the Dynamic Data if the DB Read does not display the array under the Model ID?

I hope this is not a duplicate post, it feels like I have been searching for days to find an answer to this.

Gr

GreyHead 29 Jan, 2016

Answer

2 Likes

Hi relliott,

I had a dig around in the code and think I have a workaround hack for this.

Edit the file /administrator/components/com_chronoforms5/chronoforms/actions/db_read/db_read.php around line 108 like this

			if($config->get('enable_relations', 0)){
				//$form->data = array_merge($form->data, $rows); // comment out the existing line
				// add the following lines
				foreach ( $rows as $k => $r ) {
					$rows[$k] = array_reduce($r, 'array_merge', array());
				}
				$form->data[$model_id] = $rows;
				// end of hack
			}else{

On my test this produces a result like this

    [content] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [title] => Getting Started
                    [created_by] => 455
                    [username] => greyhead
                )

            [1] => Array
                (
                    [id] => 2
                    [title] => My first article
                    [created_by] => 455
                    [username] => greyhead
. . .

where I am loading from #__contentst and joining #__users to get the username

Bob

re

relliott 29 Jan, 2016

Hey Bob,

That worked like a charm.

Thank you so much!

Ryan

ti

tim92109 01 Apr, 2016

After trying this hack on the relation tab it still doesn't populate the dropdown. I've tried switching the tables between the main table and the relations tab, tried separating into 2 different DB Reads, and tried dozens of different configurations. After 5 days of this, I'm convinced that the relations tab just doesn't work.

Basically, I'm trying to get the field cb_com_company from the second table (using hasMany) to display in a dropdown.

These are what I believe are relevant settings:

Edit Action:DB Read (basic tab)

model id: connection

fields: referenceid,memberid,accepted

conditions:

Code: Select all Expand
<?php
$user = JFactory::getUser();
return array('connection.memberid' => $user->id, 'connection.accepted' => '1');
?>
Edit Action:DB Read (relations tab)

model: profile

type: hasMany

foreign key: id

Join Conditions:

Code: Select all Expand
<?php
$user = JFactory::getUser();
return array('profile.id' => 'connection.referenceid');
?>
All this might be irrelevant as the debugger shows the arrays exactly as I would expect. I'm trying to show the field 'cb_com_company' under the model id 'profile'.

Under the 'dynamic data' tab of the dropdown field, I have the following set...

data path: profile

value key: cb_com_company

text key: cb_com_company

Especially after seeing the field I need in the correct model ID below in arrays, I'm surprised this doesn't work...

Code: Select all Expand
Array
(
[option] => com_chronoforms5
[chronoform] => sendform
[0] => Array
(
[connection] => Array
(
[referenceid] => 496
[memberid] => 497
[accepted] => 1
)

[profile] => Array
(
[0] => Array
(
[id] => 496
[user_id] => 496
[firstname] =>
[middlename] =>
[lastname] =>
[hits] => 30
[message_last_sent] => 0000-00-00 00:00:00
[message_number_sent] => 0
[avatar] =>
[avatarapproved] => 1
[canvas] =>
[canvasapproved] => 1
[approved] => 1
[confirmed] => 1
[lastupdatedate] => 0000-00-00 00:00:00
[registeripaddr] => 98.150.102.226
[cbactivation] =>
[banned] => 0
[banneddate] =>
[unbanneddate] =>
[bannedby] =>
[unbannedby] =>
[bannedreason] =>
[acceptedterms] => 0
[cb_invite_code] =>
[cb_accounttype] => Company
[cb_com_company] => TJ Inc.
[cb_ind_address] =>
[cb_ind_phone] =>
[cb_ind_position] =>
[cb_ind_auth] =>
[cb_ind_cert] =>
[cb_ind_issuedate] =>
[cb_ind_upload_certification] =>
[cb_com_address] => 123 Main St
San Diego, CA 92101
[cb_com_phone] => 18588668244
[cb_com_position] => CEO
[cb_com_cert] => 11211
[cb_com_auth] => 22122
[cb_com_upload_certification] => cb_com_upload_certification_496_56b2327957fe0.jpg
[cb_privacy_profile] =>
[cb_privacy_disable_me] =>
[cb_privacy_delete_me] =>
[invite_code] =>
[cb_archivedforms] =>
[cb_ind_upload_id] =>
[cb_ind_upload_idapproved] => 1
[cb_ind_upload_certificationapproved] => 1
[cb_com_upload_certificationapproved] => 1
[cb_com_upload_id] => cb_com_upload_id_496_56b232796f88d.PNG
[cb_com_upload_idapproved] => 1
[cb_ind_name] =>
[cb_com_admin] => Timothy Johns
[cb_test] =>
)
)
)

[1] => Array
(
[connection] => Array
(
[referenceid] => 513
[memberid] => 497
[accepted] => 1
)

[profile] => Array
(
[0] => Array
(
[id] => 513
[user_id] => 513
[firstname] =>
[middlename] =>
[lastname] =>
[hits] => 7
[message_last_sent] => 0000-00-00 00:00:00
[message_number_sent] => 0
[avatar] =>
[avatarapproved] => 1
[canvas] =>
[canvasapproved] => 1
[approved] => 1
[confirmed] => 1
[lastupdatedate] => 2016-03-22 16:39:26
[registeripaddr] => 72.168.177.225
[cbactivation] =>
[banned] => 0
[banneddate] =>
[unbanneddate] =>
[bannedby] =>
[unbannedby] =>
[bannedreason] =>
[acceptedterms] => 0
[cb_invite_code] =>
[cb_accounttype] => Company
[cb_com_company] => Aron's Aircraft Services
[cb_ind_address] =>
[cb_ind_phone] =>
[cb_ind_position] =>
[cb_ind_auth] =>
[cb_ind_cert] =>
[cb_ind_issuedate] =>
[cb_ind_upload_certification] =>
[cb_com_address] => 1234 Main Street
Tampa Florida 34606
[cb_com_phone] => 727-514-2860
[cb_com_position] => Aron Sword
[cb_com_cert] => XR4R32SW
[cb_com_auth] => XR345678UY
[cb_com_upload_certification] => cb_com_upload_certification_513_56ead94f81367.jpg
[cb_privacy_profile] =>
[cb_privacy_disable_me] =>
[cb_privacy_delete_me] =>
[invite_code] =>
[cb_archivedforms] =>
[cb_ind_upload_id] =>
[cb_ind_upload_idapproved] => 1
[cb_ind_upload_certificationapproved] => 1
[cb_com_upload_certificationapproved] => 1
[cb_com_upload_id] =>
[cb_com_upload_idapproved] => 1
[cb_ind_name] =>
[cb_com_admin] => Aron Sword
[cb_test] =>
)
)
)
)
DEBUG INFO

Code: Select all Expand
Array
(
[0] => Array
(
[DB Read] => Array
(
[Queries] => Array
(
[0] => SELECT `connection`.`referenceid` AS `connection.referenceid`, `connection`.`memberid` AS `connection.memberid`, `connection`.`accepted` AS `connection.accepted` FROM `2sc_comprofiler_members` AS `connection` WHERE `connection`.`memberid` = '497' AND `connection`.`accepted` = '1'
[1] => SELECT `profile`.`id` AS `profile.id`, `profile`.`user_id` AS `profile.user_id`, `profile`.`firstname` AS `profile.firstname`, `profile`.`middlename` AS `profile.middlename`, `profile`.`lastname` AS `profile.lastname`, `profile`.`hits` AS `profile.hits`, `profile`.`message_last_sent` AS `profile.message_last_sent`, `profile`.`message_number_sent` AS `profile.message_number_sent`, `profile`.`avatar` AS `profile.avatar`, `profile`.`avatarapproved` AS `profile.avatarapproved`, `profile`.`canvas` AS `profile.canvas`, `profile`.`canvasapproved` AS `profile.canvasapproved`, `profile`.`approved` AS `profile.approved`, `profile`.`confirmed` AS `profile.confirmed`, `profile`.`lastupdatedate` AS `profile.lastupdatedate`, `profile`.`registeripaddr` AS `profile.registeripaddr`, `profile`.`cbactivation` AS `profile.cbactivation`, `profile`.`banned` AS `profile.banned`, `profile`.`banneddate` AS `profile.banneddate`, `profile`.`unbanneddate` AS `profile.unbanneddate`, `profile`.`bannedby` AS `profile.bannedby`, `profile`.`unbannedby` AS `profile.unbannedby`, `profile`.`bannedreason` AS `profile.bannedreason`, `profile`.`acceptedterms` AS `profile.acceptedterms`, `profile`.`cb_invite_code` AS `profile.cb_invite_code`, `profile`.`cb_accounttype` AS `profile.cb_accounttype`, `profile`.`cb_com_company` AS `profile.cb_com_company`, `profile`.`cb_ind_address` AS `profile.cb_ind_address`, `profile`.`cb_ind_phone` AS `profile.cb_ind_phone`, `profile`.`cb_ind_position` AS `profile.cb_ind_position`, `profile`.`cb_ind_auth` AS `profile.cb_ind_auth`, `profile`.`cb_ind_cert` AS `profile.cb_ind_cert`, `profile`.`cb_ind_issuedate` AS `profile.cb_ind_issuedate`, `profile`.`cb_ind_upload_certification` AS `profile.cb_ind_upload_certification`, `profile`.`cb_com_address` AS `profile.cb_com_address`, `profile`.`cb_com_phone` AS `profile.cb_com_phone`, `profile`.`cb_com_position` AS `profile.cb_com_position`, `profile`.`cb_com_cert` AS `profile.cb_com_cert`, `profile`.`cb_com_auth` AS `profile.cb_com_auth`, `profile`.`cb_com_upload_certification` AS `profile.cb_com_upload_certification`, `profile`.`cb_privacy_profile` AS `profile.cb_privacy_profile`, `profile`.`cb_privacy_disable_me` AS `profile.cb_privacy_disable_me`, `profile`.`cb_privacy_delete_me` AS `profile.cb_privacy_delete_me`, `profile`.`invite_code` AS `profile.invite_code`, `profile`.`cb_archivedforms` AS `profile.cb_archivedforms`, `profile`.`cb_ind_upload_id` AS `profile.cb_ind_upload_id`, `profile`.`cb_ind_upload_idapproved` AS `profile.cb_ind_upload_idapproved`, `profile`.`cb_ind_upload_certificationapproved` AS `profile.cb_ind_upload_certificationapproved`, `profile`.`cb_com_upload_certificationapproved` AS `profile.cb_com_upload_certificationapproved`, `profile`.`cb_com_upload_id` AS `profile.cb_com_upload_id`, `profile`.`cb_com_upload_idapproved` AS `profile.cb_com_upload_idapproved`, `profile`.`cb_ind_name` AS `profile.cb_ind_name`, `profile`.`cb_com_admin` AS `profile.cb_com_admin`, `profile`.`cb_test` AS `profile.cb_test` FROM `2sc_comprofiler` AS `profile` WHERE `profile`.`id` IN ('496', '513')
)
)
)
)

AFTER HACK:
(still doesn't work)...

Array
(
[option] => com_chronoforms5
[chronoform] => TESTsendform
[connection] => Array
(
[0] => Array
(
[referenceid] => 496
[memberid] => 497
[accepted] => 1
[0] => Array
(
[id] => 496
[user_id] => 496
[firstname] =>
[middlename] =>
[lastname] =>
[hits] => 30
[message_last_sent] => 0000-00-00 00:00:00
[message_number_sent] => 0
[avatar] =>
[avatarapproved] => 1
[canvas] =>
[canvasapproved] => 1
[approved] => 1
[confirmed] => 1
[lastupdatedate] => 0000-00-00 00:00:00
[registeripaddr] => 98.150.102.226
[cbactivation] =>
[banned] => 0
[banneddate] =>
[unbanneddate] =>
[bannedby] =>
[unbannedby] =>
[bannedreason] =>
[acceptedterms] => 0
[cb_invite_code] =>
[cb_accounttype] => Company
[cb_com_company] => TJ Inc.

mf

mfeindt 05 Apr, 2016

hi...

this hack worked for me too, so thanks for that.

But will this hack ever merged in the final component. After the last update the my main form didn't work again and it takes me hours to find out, that this hack was overritten :-( .

Kind regards
Matthias

This topic is locked and no more replies can be posted.