Forums

After reading several threads to that topic (one was marked as "solved" although the problem was unanswered) I tried to join two tables:

DBMRL1 (participants):
Table -> is12_participants
ModelID -> ParticipantsList
Load Data -> Yes
Data Load Type -> All
Enable Associations -> Yes
JOIN Type -> Left
JOIN Rule -> ParticipantsList.cf_country=CountryCode.country_code
Associated Models -> CountryCode
WHERE Statement -> 1 ORDER BY country_name, cf_lastname, cf_firstname


DBMRL2 (country-code):
Table -> is12_countrycode
ModelID -> CountryCode
Load Data -> No
Enable Associations -> No

I expected a Query like this:

"SELECT * FROM is12_participants LEFT JOIN is12_countrycode ON is12_participants.cf_country = is12_countrycode.country_code WHERE 1 ORDER BY country_name, cf_lastname, cf_firstname"

Getting no data, I let the debugger find out that the query made by CF looks like this:

"SELECT `ParticipantsList`.* FROM `is12_participants` AS `ParticipantsList` WHERE 1 ORDER BY country_name, cf_lastname, cf_firstname"

So the JOIN command is missing at all and the SELECT command gets a wrong parameter `ParticipantsList`. connected to the asterisk.

How can this be fixed?

Hi HerKle,

I've never used this so I could well be wrong!! but from reading Max's help I think the Join Type and Rule need to be set on the secondary DB Multi Record Loaders rather than the primary one.

Bob

Sorry. That didn't help either. It's the same result.

And btw it's a bit weird to describe an association which you just ticked "no"... :-)

So maybe this Max could help?

Hi,

Yes, Bob is correct, you should set the Join type and rule in secondary DBMRL actions, what's the query you have generated now ?

And please try to set "Enable Associations" to "Yes" under both actions.

Regards,
Max

Now I document my complete input in both loaders. Maybe my mistake is hidden somewhere else here:

DBMRL1 (participants):
Basic
DB Field (empty)
Table -> is12_participants
Request Param (empty)
ModelID -> ParticipantsList2
Fields (empty)

Advanced
Load Data -> Yes
Data Load Type -> All
Enable Associations -> Yes
JOIN Type -> Inner (there is no option to "not set" the type)
JOIN Rule (empty)
Associated Models -> CountryCode
Group Model Data -> No
WHERE Statement -> 1 ORDER BY country_name, cf_lastname, cf_firstname

Data Displayer
Enable Data Displayer -> No
Display Fields (empty)
Other Fields (empty)
Enable Pagination -> No
Limit -> 50


DBMRL2 (country-code):
Basic
DB Field (empty)
Table -> is12_countrycode
Request Param (empty)
ModelID -> CountryCode
Fields (empty)

Advanced
Load Data -> No
Data Load Type -> All
Enable Associations -> Yes
JOIN Type -> Left
JOIN Rule -> ParticipantsList2.cf_country=CountryCode.country_code
Associated Models -> ParticipantsList2
Group Model Data -> No
WHERE Statement (empty)

Data Displayer
Enable Data Displayer -> No
Display Fields (empty)
Other Fields (empty)
Enable Pagination -> No
Limit -> 50

The debugger on On Empty Result tells:

(
    [Itemid] => 519
    [option] => com_chronoforms
    [view] => form
    [ParticipantsList2] => 
)

and the query looks like this (again):

SELECT `ParticipantsList2`.* FROM `is12_participants` AS `ParticipantsList2` WHERE 1 ORDER BY country_name, cf_lastname, cf_firstname

I am running out on ideas...

Hi HerKle,

Ok, 2 things:

#1- disable the "Enable associations" in DBMRL #2
#2- Actually, in order for this to work, the main DBMRL should be AFTER the secondary ones, so the order of actions in the event should be: DBMRL#2 then DBMRL#1

Regards,
Max

OMG, THAT explains it: both DBMRL in ONE event. Before I had two separate forms and one DBMRL in each. I already presumed I must have done s.th. stupid.

Now everything works fine, except the array I get:

[5] => Array
                (
                    [cf_country] => DE
                    [cf_lastname] => Kleinschmidt
                    [cf_firstname] => Herbert
                    [cf_sex] => M
                    [cf_city] => Maintal
                    [CountryCode] => Array
                        (
                            [country_name] => Germany
                        )

                )

How can I get the results of the second table in the same array as the ones of the first table? For further processing it would simplify the task, esp. when it's only one field (country_name) concerned which is put into this second extra sub-array.

Thanks for your assistance.
Herb

Max,

Having tackled the dynamic dropdown problem, I put myself to the next challenge and that is joining 5 tables using the DBMRL. The query is generated correctly:

SELECT `job_details`.*, `c2`.`country` AS `c2.country`, `ct`.`cityname` AS `ct.cityname`, `c1`.`country` AS `c1.country`, `cdrt`.`city_entered` AS `cdrt.city_entered` 
FROM `j02xa_jobs` AS `job_details` 
LEFT JOIN `j02xa_chronoforms_data_Register_Transporter` AS `cdrt` ON cdrt.cf_user_id = job_details.user_id 
LEFT JOIN `j02xa_country` AS `c1` ON cdrt.iso = c1.isocty 
LEFT JOIN `j02xa_cities` AS `ct` ON job_details.destination_id = ct.ID 
LEFT JOIN `j02xa_country` AS `c2` ON ct.isocity=c2.isocty 
WHERE `job_id` = '7' 

This query returns one row (as expected) when run directly in phpmyadmin.

In CF, however, the following is returned:

[job_details] => Array
        (
            [job_id] => 7
            [destination_id] => 18808
            [max_delivery_date] => 2012-08-17
            [freight_type] => Fragile
            [user_id] => 291
            [c2] => Array
                (
                    [country] => Greece
                )
            [ct] => Array
                (
                    [cityname] => Daphne
                )
            [c1] => Array
                (
                    [country] => Netherlands
                )
            [cdrt] => Array
                (
                    [city_entered] => Zwolle
                )
        )

But only when I set 'Data Load Type' to 'First Record'. If set to 'ALL', I get

[job_details] => Array
        (
            [0] => Array
                (
                    [job_id] => 7
                    [destination_id] => 18808
                    [max_delivery_date] => 2012-08-17
                    [freight_type] => Fragile
                    [user_id] => 291
                    [c2] => Array
                        (
                        )
                    [ct] => Array
                        (
                        )
                    [c1] => Array
                        (
                        )
                    [cdrt] => Array
                        (
                        )
                )
        )

How can I get all results in one array?

Hi MichielStr,

I don't know the exact answer to your query without building a test form and I don't have the time to do that today. I suspect that the answer will lie with the Model IDs.

However, once the query gets this complex I'd probably forget the DB Record Loader actions and hand-code the query into a Custom Code action and load the result directly into the $form->data array. It gives you more control and is much simpler to debug.

Bob

PS For other readers: Please see this FAQ on linking two DB Multi-Record Loaders.

Bob

Hi Bob,

My idea, exactly. I, too, believe this has something to do with the model ID's (cross linking).

I have this working in a php script outside of CF, but wanted to take the 'official' route, since the page also involves forms.

I will do as you suggested and take the Custom Code approach.

Brgds,

Michiel