databasejoin element doesn't load from external database

[h.kleinveld]

When I go to the list view of that list which has a databasejoin element that loads a list off a second database, I get this for example:

500 - Fabrik has generated an incorrect query for the list :
 
Table 'genplan_jos1.catalog_product_entity' doesn't exist SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `therapeut_supplementen`.`id` AS `therapeut_supplementen___id`,
`therapeut_supplementen`.`id` AS `therapeut_supplementen___id_raw`,
`therapeut_supplementen`.`date_time` AS `therapeut_supplementen___date_time`,
`therapeut_supplementen`.`date_time` AS `therapeut_supplementen___date_time_raw`,
`therapeut_supplementen`.`Naam` AS `therapeut_supplementen___Naam`,
`therapeut_supplementen`.`Naam` AS `therapeut_supplementen___Naam_raw`,
`therapeut_supplementen`.`shop_product` AS `therapeut_supplementen___shop_product`,
`therapeut_supplementen`.`Aantal` AS `therapeut_supplementen___Aantal`,
`therapeut_supplementen`.`Aantal` AS `therapeut_supplementen___Aantal_raw`,
`therapeut_supplementen`.`Eenheid_supplement` AS `therapeut_supplementen___Eenheid_supplement_raw`,
`therapeut_eenheden`.`Naam_enkelvoud` AS `therapeut_supplementen___Eenheid_supplement`,
`therapeut_supplementen`.`Verpakking_Supplement` AS `therapeut_supplementen___Verpakking_Supplement_raw`,
`therapeut_verpakkingen`.`Naam_enkelvoud` AS `therapeut_supplementen___Verpakking_Supplement`,
`therapeut_supplementen`.`Advies` AS `therapeut_supplementen___Advies`,
`therapeut_supplementen`.`Advies` AS `therapeut_supplementen___Advies_raw`,
`therapeut_supplementen`.`id` AS slug
, `therapeut_supplementen`.`id` AS `__pk_val`
 
FROM `therapeut_supplementen`
LEFT JOIN  `therapeut_eenheden` AS `therapeut_eenheden` ON `therapeut_eenheden`.`id` = `therapeut_supplementen`.`Eenheid_supplement`
LEFT JOIN  `therapeut_verpakkingen` AS `therapeut_verpakkingen` ON `therapeut_verpakkingen`.`id` = `therapeut_supplementen`.`Verpakking_Supplement`
LEFT JOIN  `catalog_product_entity` AS `catalog_product_entity` ON `catalog_product_entity`.`entity_id` = `therapeut_supplementen`.`shop_product` LIMIT 0, 10

And this table catalog_product_entity does not exist in the main database, but in the external database, called michael_magento. For some reason it worked before, but not anymore.

 

[Bauer]

In the list's 'data' tab - is that secondary database shown as the database that the list is using?
If so, in the main fabrik page go into 'Connections' and verify that the database you think you are using for that list is shown in the list of fabrik 'Connections'. If it is, click 'Test connection' to verify it is correct. If the test fails, edit the connection to fix it (then re-test).
If the 'Connection' that is shown when you edit the list is not correct, you would have to recreate the list as you can't change the database used by a list once it has been saved.

Or I suppose maybe you could try to manually edit the #__fabrik_lists table via phpMyAdmin and change it there. Open the #__fabrik_lists table and find the fabrik list in question and set the 'connection_id' to use the id of that secondary connection you just tested. Hopefully that will solve the problem.

 

[troester]

Joining across connetions is not supported.
http://fabrikar.com/forums/index.php?wiki/database-join-element/#data

 

[Bauer]

Joining across connetions is not supported.
http://fabrikar.com/forums/index.php?wiki/database-join-element/#data

Well that explains it. I wish I had your knowledge - or maybe I should spend more time reading the Wiki.

 

[h.kleinveld]

Ai, shit. It used to work though. I was linking products in one system to products in the webshop. Was it giving too much trouble, and will it be again so in the future? It sort of ruins my whole concept.

 

[troester]

A workaround:
create a MySQL view to the external table in your main database, use this view in your dbjoin element.