Form databasejoin Element inner join

[stausen]

Hi,
I'm frustated because since two days I'm not able to get the right result from the form database join element.

table 'Adresse'
I need the field 'firma' as label of checkbox

table 'Auftrag'
I need the field 'kundennummer' as value of checkbox

the join field in both tables is AdresseID.

How is this result possible??

 

[cheesegrits]

Inner join? Where are you getting that from? The only "inner join" we provide is on list joins, but they have nothing to do with checkboxes.

Can you explain in more details what you are trying to achieve?

-- hugh

 

[stausen]

Hi and thanks for reply,

for my form I need a selection of my customers form another database. Therefor I'm using the databasejoin element. The label of the checkboxes should be the field firma from the table Adresse but the value of the checkboxes should be the field kundennummer from the table Auftrag. The key field for join is AdresseID in both tables.... I hope you understand what I'm talking about and I hope this is possible.

 

[troester]

you can use the Concat, something like
(SELECT firma from Adresse WHERE Adresse.AdresseID = {thistable}.AdresseID)

 

[stausen]

Yeeees.... It works!!!
Thanks a lot. I was trying it the all the time in the data where fields. Fine that I can use sql querys in this field.

You made my day!

 

[stausen]

Hi,
me once again. Because of the databasejoin element to another database fabrik is not able to show me the data in list view. It is seaching in the joom database for a table called Adresse....
Here's the error message:

Table 'usr_web54_1.Adresse' doesn't exist SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `listtest`.`meilenstein_ausgabe` AS `listtest___meilenstein_ausgabe`,
`listtest`.`meilenstein_ausgabe` AS `listtest___meilenstein_ausgabe_raw`,
`listtest`.`datetime` AS `listtest___datetime`,
`listtest`.`datetime` AS `listtest___datetime_raw`,
`listtest`.`bestandskunden` AS `listtest___bestandskunden`,
`listtest`.`bestandskunden` AS `listtest___bestandskunden_raw`,
`listtest`.`id` AS `listtest___id`,
`listtest`.`id` AS `listtest___id_raw`,
(SELECT GROUP_CONCAT(CONCAT_WS('', (SELECT Adresse.firma from Adresse WHERE Adresse.AdresseID = `lookup`.AdresseID),' - ',(SELECT Adresse.vorname from Adresse WHERE Adresse.AdresseID = `lookup`.AdresseID),' ',(SELECT Adresse.nachname from Adresse WHERE Adresse.AdresseID = `lookup`.AdresseID)) SEPARATOR '//..*..//') FROM listtest_repeat_auftrags_id
LEFT JOIN Auftrag AS lookup ON lookup.kundennummer = listtest_repeat_auftrags_id.auftrags_id WHERE listtest_repeat_auftrags_id.parent_id = `listtest`.`id`) AS listtest___auftrags_id,
(SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM listtest_repeat_auftrags_id WHERE parent_id = `listtest`.`id`) AS `listtest___auftrags_id_raw`, (SELECT GROUP_CONCAT(auftrags_id SEPARATOR '//..*..//') FROM listtest_repeat_auftrags_id WHERE listtest_repeat_auftrags_id.parent_id = `listtest`.`id`) AS listtest___auftrags_id_id,
(SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM listtest_repeat_auftrags_id WHERE parent_id = `listtest`.`id`) AS `listtest___auftrags_id___params`,
`listtest`.`id` AS slug
, `listtest`.`id` AS `__pk_val`

How do you handle such things?

 

[cheesegrits]

We don't support cross database joining.

We provide the connection option so you can put all of your tables on another connection if you want, but you can't mix and match.

-- hugh

 

[troester]

Maybe in this case (because it'a a "manual" query) you can add the database directly before the table (WHERE `your-2nd-db`.Adresse.AdresseID)
I didn't try and I don't know if this may have side effects

 

[cheesegrits]

That might work, as long as the credentials are identical (database user and password).

-- hugh

 

[automill]

You can link and make another DataBase as "Slave".

If you can handle PHP (where and how to put) in your project, use it as herebelow. The best way to create PHP file in [components/com_fabrik/php] and put the code from Step-1 to Step-3 and include it before custom query build [in step-4] in Forms/elements.

Step 1: First setup your connection options:

$option['driver'] = 'MySQLi';
$option['host'] = 'localhost';
$option['user'] = 'root';
$option['password'] = '';
$option['database'] = 'joomla_slave';
$option['prefix'] = 'xxxx_';

Step 2: Now make instance of `JDatabase` with connection option:

$db_slave = & JDatabase::getInstance($option);

Step 3: Setup error handling:

if ( JError::isError($db_slave) ) {
            jexit('Database Error: ' . $db_slave->toString() );
}
if ($db_slave->getErrorNum() > 0) {
            JError::raiseError(500, 'JDatabase::getInstance: Could not connect to database <br />');
}

Step 4: Use the object and combine queries from your Master & Slave DataBase:

$db_master = & JFactory::getDbo();
$query = 'SELECT * FROM `#__users`';
$db_master->setQuery( $query );
$rows = $db_master->loadObjectList();             
echo '<pre>';
print_r($rows);
echo '</pre>';
echo '------------------------------';
$query = 'SELECT * FROM `#__users`';
$db_slave->setQuery( $query );
$rows = $db_slave->loadObjectList();                
echo '<pre>';
print_r($rows);
echo '</pre>';

Replace "echo" with "return" if use in fabrik Forms/Elements.

Regards,
Sunit